I’ve ran in the past into a few issues with how the Average Cycle Time is calculated in the Cycle Time Widget, so I decided to analyze it with Python to see if I find any way to calculate the Average Cycle Time and get the same value displayed in the Cycle Time Widget.

My issue is that I can´t reach the same value for Average Cycle Time that is shown in the Cycle Time Widget.

Can you guys help me figure out this?

At the time of writing this topic, the value for Average Cycle Time that is shown in the Cycle Time Widget is 12 days.

However, using Python, Pandas and connecting with the Feed OData API, I never reach the same value. I’ve reach the value of 11 days, and using other calculation methods, like moving averages, I’ve reached either 9 days or 11 days. I’ve provided the code in my post.

When using Power BI and connecting with the Feed OData API, the way that is instructed here, the value I get for Average Cycle Time is still 11.

At the time of writing this topic, the value I was targeting were 12 days.

Here’s my code – originally written in a Jupyter notebook:

# %%
import pandas as pd
import requests
import json
import base64
import math
from datetime import datetime, timedelta

# %%
token_do_azure = '{hidden}'
pat_encoded = base64.b64encode((":" + token_do_azure).encode()).decode()
headers = {
    'Content-Type': 'application/json',
    'Authorization': f'Basic {pat_encoded}'
}

# %%
hoje = datetime.today()

delta = timedelta(days=90)
dia_resultante = hoje - delta

dia_formatado = dia_resultante.strftime('%Y-%m-%dT00:00:00.00000Z')

print(dia_formatado)

url = rf"https://analytics.dev.azure.com/{hidden}/_odata/v4.0-preview/WorkItems?$select=WorkItemId,WorkItemType,Title,CycleTimeDays,ClosedDate&$filter=(Project/ProjectName eq 'Suporte_Torres' AND (WorkItemType eq 'Issue') AND State eq 'Done' AND ClosedOn/Date ge {dia_formatado})"

# %%
req = requests.get(url, headers=headers)
req_tabela = json.loads(req.text)
req_valores = req_tabela["value"]

# %%
df = pd.DataFrame(req_valores)
df['ClosedDate'] = pd.to_datetime(df['ClosedDate'], format='ISO8601').dt.date

# %%
print(round(df['CycleTimeDays'].mean(), 0))
# Returns 11.0, instead of 12.0.

# %%
# The moving average part of the code was written based on the moving average content of this page: https://learn.microsoft.com/en-us/azure/devops/report/dashboards/cycle-time-and-lead-time?view=azure-devops
def calcular_janela_n(n_dias):
    n = int(0.2 * n_dias)
    n = math.floor(n)
    if n % 2 == 0:
        n -= 1
    if n < 1:
        n = 1
    return n

janela_n = calcular_janela_n(90)

# %%
df['SMA_n'] = df['CycleTimeDays'].rolling(window=janela_n, min_periods=1).mean()
print(round(df['SMA_n'].tail(1).iloc[0], 0))
# Returns 9.0, instead of 12.0.
print(round(df['SMA_n'].mean(), 0))
# Returns 11.0, instead of 12.0.