Search code examples
pythonazure-devopspowerbiodataazure-boards

Average Cycle Time is different between my Python code and Cycle Time widget

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.
Solution

  • I attempt with the OData query like as below, and it can return the same CycleTimeDays as that on the Cycle Time Widget.

    https://analytics.dev.azure.com/myOrg/myProj/_odata/v4.0-preview/WorkItems?
    $filter=WorkItemType eq 'Task'
        and StateCategory eq 'Completed'
        and CompletedDate ge 2024-01-20Z
        and startswith(Area/AreaPath,'myProj\myArea')
    &$select=WorkItemId,WorkItemType,Title,State,ClosedDate,CycleTimeDays,LeadTimeDays
    &$expand=Iteration($select=IterationPath),Area($select=AreaPath)

    Note:

    The values of CycleTimeDays returned by the OData query are float instead of integer. The values displayed on the Cycle Time Widget are generally rounded.

    enter image description here

    EDIT:

    Below are the OData queries I catch from the browser Network trace logs (Developer tools) when I configure the Cycle Time Widget with the same settings as yours.

    1. Query #1:

      https://analytics.dev.azure.com/{organizationName}/{ProjectName}/_odata/v4.0-preview/WorkItems?
    $apply=filter(CompletedDateSK ge 20240618 and Teams/any(t:(t/TeamSK eq {teamId} and (WorkItemType eq 'Issue'))))/groupby((CompletedDateSK, WorkItemType),aggregate($count as CompletedCount,CycleTimeDays with sum as Sum,CycleTimeDays mul CycleTimeDays with sum as SumOfSquares))

      enter image description here

      Example of the response.

      {
    "@odata.context": "https://analytics.dev.azure.com/{organizationName}/{ProjectId}/_odata/v4.0-preview/$metadata#WorkItems(CompletedDateSK,WorkItemType,CompletedCount,Sum,SumOfSquares)",
    "value": [
        {
            "@odata.id": null,
            "WorkItemType": "Issue",
            "CompletedDateSK": 20240717,
            "SumOfSquares": 2394586.3654467259,
            "Sum": 2425.1853354,
            "CompletedCount": 4
        }
    ]
}
      • The value of "Sum" is the sum of cycle time for all the completed work items within the specified Time period.
      • The value of "CompletedCount" is the count of for all the completed work items within the specified Time period.
      • The value of "Days on average" displayed on Widget is calculated by "Sum/CompletedCount".

      enter image description here

    2. Query #2: Similar as the first query I posted above last time, it will return a list of cycle time for each completed work items within the specified Time period.

      https://analytics.dev.azure.com/{organizationName}/{ProjectName}/_odata/v4.0-preview/WorkItems?
    $filter=CompletedDateSK ge 20240623
        and (Teams/any(t:t/TeamSK eq {teamId}))
        and WorkItemType eq 'Issue'
    &$select=WorkItemId,Title,WorkItemType,CompletedDateSK,CycleTimeDays

    EDIT_2:

    According to the statement about "Days on average" on the documentation "Configure Lead/Cycle time widget":

    Days on average (average lead time or cycle time) for the main work item types configured for the chart. This number might not be equal to the average cycle/lead time of all work items. It depends on configurations used for widgets. The average number is calculated based on each day the team takes time for work item.

    The average days is calculated based on the working days that do not contain the weekend days, so the sum days also should be calculated based on the working days.

    I think, when setting the days of Rolling period as 30, the widget would calculate the sum days and average days within the last 30 working days (not contain weekend days).

    So, on above Query #1, the actual date value of CompletedDateSK could be more than 30 days.