Lead time and Cycle time sample report
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.
For more information 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
- Access level: You must be a member of a project with Basic access or higher.
- Permission: By default, project members have permission to query Analytics and create views.
- For more information 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
. For more information 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.
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
, andAssignedTo
. - Transform the data type for the
LeadTimeDays
andCycleTimeDays
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:
- Expand columns.
- Transform the LeadTimeDays and CycleTimeDays columns to whole Numbers.
- Transform a column data type.
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.
Create the trend chart
In the following example, the query was renamed to LeadCycleTime, but no columns were renamed.
In Power BI, choose the Line chart report under Visualizations.
Add
CompletedDateSK
to the X-axis.Add
LeadTimeDays
to the Y-axis, right-click and select Average.Add
Priority
to Legend.
The example report displays. Note that as you hover over any area in the report, more data is shown.