Edit

Lead time and Cycle time sample report

  • Article

Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019

You can create lead time and cycle charts using the queries provided in this article. Lead time and cycle time indicate how long it takes for work to flow through a team's development pipeline. Lead time measures the total time elapsed from the creation of work items to their completion. Cycle time measures the time it takes for a team to complete work items once they begin actively working on them.

The following image shows an example for average lead time broken down by priority assignments made to the user stories.

Screenshot of Power BI Lead Cycle Time trend chart report.

To learn more about lead and cycle time, see Lead Time and Cycle Time widgets and Cumulative flow, lead time, and cycle time guidance.

Note

This article assumes you've read Overview of Sample Reports using OData Queries and have a basic understanding of Power BI.

Prerequisites

  • To view Analytics data and query the service, you need to be a member of a project with Basic access or greater. By default, all project members are granted permissions to query Analytics and define Analytics views.
  • To learn about other prerequisites regarding service and feature enablement and general data tracking activities, see Permissions and prerequisites to access Analytics.

Sample queries

Queries in this section support returning lead and cycle time data for completed User Stories. You can query by area path or team name(s). All of these queries specify the WorkItems entity set as they return data calculated for each work item by the Analytics service.

Note

To determine available properties for filter or report purposes, see Metadata reference for Azure Boards. You can filter your queries or return properties using any of the Property values under an EntityType or NavigationPropertyBinding Path values available with an EntitySet. Each EntitySet corresponds to an EntityType. To learn more about the data type of each value, review the metadata provided for the corresponding EntityType.

Return Lead Time and Cycle Time for User Stories and Area Path

You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review Overview of sample reports using OData queries.

let
   Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItems?"
        &"$filter=WorkItemType eq 'User Story' "
            &"and StateCategory eq 'Completed' "
            &"and CompletedDate ge {startdate} "
            &"and startswith(Area/AreaPath,'{areapath}') "
        &"&$select=WorkItemId,Title,WorkItemType,State,Priority,AreaSK "
            &",CycleTimeDays,LeadTimeDays,CompletedDateSK "
        &"&$expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath) "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

Substitution strings and query breakdown

Substitute the following strings with your values. Don't include brackets {} with your substitution. For example if your organization name is "Fabrikam", replace {organization} with Fabrikam, not {Fabrikam}.

  • {organization} - Your organization name
  • {project} - Your team project name, or omit "/{project}" entirely, for a cross-project query
  • {areapath} - Your Area Path. Example format: Project\Level1\Level2
  • {startdate} - Start your report for items completed on or after a given date with the format: YYYY-MM-DDZ. For example: 2022-04-01Z represents 2022-April-01. Don't enclose in quotes.

Query breakdown

The following table describes each part of the query.

Query part

Description

$filter=WorkItemType eq 'User Story'

Return data for User Stories.

and StateCategory eq 'Completed'

Return only completed items. The system calculates values for Lead Time Days and Cycle Time Days only for work items that have completed.

Note

If you look at the raw data of records, you may see entries where CycleTimeDays=0. This occurs when the User Story or work item enters an In Progress workflow category state and Completed on the same day. For more information on workflow state categories, see How workflow states and state categories are used in Backlogs and Boards.

and CompletedDate ge {startdate}

Return items that are completed after the specified date. Example: 2022-04-01Z represents 2022-April-01

and startswith(Area/AreaPath,'{areapath}')

Return work items under a specific Area Path that you specify in'{areapath}'. To filter by team name, use the filter statement Teams/any(x:x/TeamName eq '{teamname})'.

&$select=WorkItemId, Title, WorkItemType, State, Priority

Select properties to return.

, CycleTimeDays, LeadTimeDays, CompletedDateSK

Return the properties that support Lead Time Days, Cycle Time Days and CompletedDateSK. CompletedDateSK returns a date value as an integer

&$expand=AssignedTo($select=UserName), Iteration($select=IterationPath), Area($select=AreaPath)

Expand the properties for AssignedTo, Iteration, and Area entities and select entity fields for them.

Return Lead Time and Cycle Time for User Stories and teams

This query is the same as the one provided in the previous section, except it filters by several team names rather than area paths.

You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review Overview of sample reports using OData queries.

let
   Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItems?"
        &"$filter=WorkItemType eq 'User Story' "
            &"and StateCategory eq 'Completed' "
            &"and CompletedDate ge {startdate} "
            &"and (Teams/any(x:x/TeamName eq '{teamname}) or Teams/any(x:x/TeamName eq '{teamname}) or Teams/any(x:x/TeamName eq '{teamname}) "
        &"&$select=WorkItemId,Title,WorkItemType,State,Priority,AreaSK "
            &",CycleTimeDays,LeadTimeDays,CompletedDateSK "
        &"&$expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath) "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

(Optional) Rename query

You can rename the default query label, Query1, to something more meaningful. Simply enter a new name from the Query Settings pane.

Screenshot of Power BI query menu options, rename query.

Transform data in Power Query Editor

From the Power Query Editor, choose the query with the data you want to transform. For a Lead/Cycle time chart, you'll want to make the following transformations:

  • Expand columns such as Area, Iteration, and AssignedTo.
  • Transform the data type for the LeadTimeDays and CycleTimeDays columns to a Whole number.
  • Transform the data type for the CompletedDateSK column from an integer to a date value.

To learn how, see the following sections in Transform Analytics data to generate Power BI reports:

Close the query and apply your changes

Once you've completed all your data transformations, choose Close & Apply from the Home menu to save the query and return to the Report tab in Power BI.

Screenshot of Power Query Editor Close and Apply option.

Create the trend chart

In the following example, the query was renamed to LeadCycleTime, but no columns were renamed.

  1. In Power BI, choose the Line chart report under Visualizations.

    Screenshot of Power BI Visualizations and Fields selections for Lead Time trend chart report.

  2. Add CompletedDateSK to the X-axis.

  3. Add LeadTimeDays to the Y-axis, right-click and select Average.

  4. Add Priority to Legend.

The example report displays. Note that as you hover over any area in the report, more data is shown.

Screenshot of Sample Power BI Lead Cycle Time trend chart report.