Lead/cycle time sample report

Azure DevOps Services | Azure DevOps Server 2019

This article shows you how to display average lead time or cycle time for a given set of User Stories. The following image shows an example for average lead time. To learn more about lead and cycle time, see Cumulative flow, lead time, and cycle time guidance.

Power BI + OData - Lead Cycle Report by Priority

Note

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

Sample queries

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,Severity,TagNames,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

Each query contains the following strings that you must substitute 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/after a given date. Format: YYYY-MM-DDZ. Example: 2019-04-01Z represents 2019-April-01. Do not enclose in quotes.

Query breakdown

The following table describes each part of the query.

Query partDescription
$filter=WorkItemType eq 'User Story'Return User Stories
and StateCategory eq 'Completed'Return only completed items. Only completed items have Lead/Cycle Times calculated. For more information on State Categories, see How workflow states and state categories are used in Backlogs and Boards.
and CompletedDate ge {startdate}Return items Closed after the specified date. Example: **2019-04-01Z** represents 2019-April-01
and startswith(Area/AreaPath,'{areapath}')Work items under a specific Area Path. Replacing with "Area/AreaPath eq '{areapath}'" returns items at a specific Area Path.
To filter by Team Name, use the filter statement Teams/any(x:x/TeamName eq '{teamname})'
&$select=WorkItemId, Title, WorkItemType, State, Priority, Severity, TagNamesSelect fields to return
, CycleTimeDays, LeadTimeDays, CompletedDateSKAlso return the Cycle/Lead Time fields, as well as CompletedDateSK. CompletedDateSK is the CompletedDate as an integer
&$expand=AssignedTo($select=UserName), Iteration($select=IterationPath), Area($select=AreaPath)Expand Assigned To, Iteration, Area entities and select entity fields

Power BI transforms

Expand Area, Iteration, AssignedTo columns

The query returns several columns that you need to expand before you can use them in Power BI. Any entity pulled in using an OData $expand statement returns a record with potentially several fields. You need to expand the record to flatten the entity into its fields. Examples of such entities are: AssignedTo, Iteration, and Area.

After closing the Advanced Editor and while remaining in the Power Query Editor, select the expand button on the entities you need to flatten.

  1. Choose the expand button.

    Power BI + OData - expanding an entity column

  2. Select the fields to flatten.

    Power BI + OData - expanding an entity column

  3. The table now contains entity field(s).

    Power BI + OData - expanding an entity column

  4. Repeat steps 1 through 3 for all fields representing entities: Area, Iteration, AssignedTo.

Change LeadTimeDays and CycleTimeDays to data type: Whole Number

The LeadTimeDays and CycleTimeDays are decimal fields. For example if Lead Time is 10 and 1/2 days, the value is 10.5. Since most Lead/Cycle Time reports assume that this is rounded to the nearest day, we need to convert these fields to an Integer. Making this conversion converts all values less than 1 to 0.

  1. Select the LeadTimeDays column by clicking the column header.
  2. Select the Transform menu.
  3. Select Data Type and change to Whole Numbers.
  4. Repeat for CycleTimeDays.

Change CompletedDateSK to a Date field

The CompletedDateSK field is the integer version of the Completed Date field in the format YYYYMMDD. For example the integer value of 2019-July-01 is 20190701. For easier reporting, we change this to a Date field.

  1. Select the CompletedDateSK column by choosing the column header.
  2. Select the Transform menu.
  3. Select Data Type and change to Text.
  4. Select Date Type (again) and change to Date.
  5. When the Change Column Type dialog appears, select Add new step (rather than Replace current step). This 2-step process is the easiest way to change it to a proper Date field in Power BI.

Rename fields and query, then Close & Apply

When finished, you may choose to rename columns.

  1. Right-click a column header and select Rename...

    Power BI Rename Columns

  2. You also may want to rename the query from the default Query1, to something more meaningful.

    Power BI Rename Query

  3. Once done, choose Close & Apply to save the query and return to Power BI.

    Power BI Close & Apply

Create the report

Power BI shows you the fields you can report on.

Note

The example below assumes that no one renamed any columns.

Power BI + OData - Lead Cycle Fields

For a simple report, do the following steps:

  1. Select Power BI Visualization Line chart.
  2. Add the field "CompletedDateSK" to Axis.
    • Right-click "CompletedDateSK" and select "CompletedDateSK", rather than Date Hierarchy.
  3. Add the field "Priority" to legend.
  4. Add the field "LeadTimeDays" to Values.
    • Right-click "LeadTimeDays" field and ensure Average is selected.

The example report:

Power BI + OData - Lead Cycle Report by Priority

To pivot the report by Area Path (representing teams), add the field "Area.AreaPath" to Legend, replacing "Priority"

Power BI + OData - Lead Cycle Report by Teams

Pull in data from multiple teams

Oftentimes, you want to aggregate and compare data from multiple teams. If you are pulling in items from multiple teams, consider adding a Team slicer to your report. A Team slicer allows you to filter the existing report by Team Name.

Additional queries

You can use the following additional queries to create different but similar reports.You can use these queries with the steps defined above.

Filter by Teams, rather than Area Path

This query is the same as the one used above, except it filters by Team Name rather than 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 (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,Severity,TagNames,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

Full list of sample reports