Lead time

Lead time measures how long it takes for work to move from creation to completion. In software delivery, it represents the time required for a change to travel through the development pipeline, from the moment work begins to the moment it is successfully delivered.

Because it captures the full journey of a change, lead time is one of the core DORA metrics and reflects the overall speed of your development and release process. A lower average lead time usually indicates smaller changes, strong automation, and efficient collaboration between teams.

Before you begin

For this tile we'll use the Azure DevOps as the data source and use the WIQL data stream to query the fields we need to calculate lead times.

However the principles used to display this metric can be applied to any data source you use, such as Jira for example.

Creating an average lead time tile

This tile displays the average lead time for completed work items within the selected timeframe.

Configuring the tile

Configure the following in the tile editor:

1. Data Source: Select Azure DevOps.
2. Data Stream: Select WIQL Query.
3. Parameters > WIQL query: Enter a query to return the necessary date fields to calculate lead time. We'll included the {{timeframe.start}} and {{timeframe.end}} parameters so that the tile responds to the specified timeframe.
   For example:
   
   Copy

   SELECT
       [System.Id],
       [System.CreatedDate],
       [Microsoft.VSTS.Common.ActivatedDate],
       [Microsoft.VSTS.Common.ClosedDate],
       [System.State]
   FROM WorkItems
   WHERE
       [System.State] = 'Closed'
       AND [Microsoft.VSTS.Common.ClosedDate] >= '{{new Date(timeframe.start).toISOString().substring(0,10)}}'
       AND [Microsoft.VSTS.Common.ClosedDate] <= '{{new Date(timeframe.end).toISOString().substring(0,10)}}'
   ORDER BY
       [Microsoft.VSTS.Common.ClosedDate] DESC

4. Timeframe: Select the timeframe you want to track. Note that after adding a monitor or configuring a KPI the Use dashboard timeframe option is disabled.

5. SQL Analytics: Enable the toggle, then configure the following:
   1. SQL > Query: Enter a query to calculate the lead time. For example:
      
      Copy

      SELECT
        AVG(DATEDIFF('minute', "Created Date", "Closed Date")) AS avg_lead_time_minute
      FROM dataset1

   2. Columns: Click Edit
      next to Avg lead time and configure the following:
      1. Type: Select Minutes.
      2. Decimal places: Enter 0.
      3. Click Save to update the column.
6. Click Save to save the tile config.

Creating an lead time frequency tile

This tile helps you understand how delivery speed changes over time by highlighting trends in delivery efficiency and makes it easier to identify when lead time begins to increase.

Configuring the tile

Configure the following in the tile editor:

1. Data Source: Select Azure DevOps.
2. Data Stream: Select WIQL Query.
3. Parameters > WIQL query: Enter a query to return the necessary date fields to calculate lead time. We'll included the {{timeframe.start}} and {{timeframe.end}} parameters so that the tile responds to the specified timeframe.
   For example:
   
   Copy

   SELECT
       [System.Id],
       [System.CreatedDate],
       [Microsoft.VSTS.Common.ActivatedDate],
       [Microsoft.VSTS.Common.ClosedDate],
       [System.State]
   FROM WorkItems
   WHERE
       [System.State] = 'Closed'
       AND [Microsoft.VSTS.Common.ClosedDate] >= '{{new Date(timeframe.start).toISOString().substring(0,10)}}'
       AND [Microsoft.VSTS.Common.ClosedDate] <= '{{new Date(timeframe.end).toISOString().substring(0,10)}}'
   ORDER BY
       [Microsoft.VSTS.Common.ClosedDate] DESC

4. Timeframe: Select the timeframe you want to track. Note that after adding a monitor or configuring a KPI the Use dashboard timeframe option is disabled.

5. SQL Analytics: Enable the toggle, then configure the following:
   1. SQL > Query: Enter a query to calculate the lead time and then group and order by date. For example:
      
      Copy

      SELECT
        "Created Date" as date,
        AVG(DATE_DIFF('hour', "Created Date", "Closed Date")) AS avg_lead_time_hours
      FROM dataset1
      GROUP BY date
      ORDER By date DESC

   2. Columns: Click Edit
      next to Date and configure the following:
      1. Output Format: Enter your preferred date format. E.g. MM/dd/yyyy or dd/MM/yyyy.
      2. Click Save to update the column.
6. Visualization: Select Line then configure the following:
   1. Mapping > X-Axis: select Date.
   2. Mapping > Y-Axis: select Avg Lead Time.
   3. X-Axis > Label: Enter Date.
   4. Y-Axis > Label: Enter Avg Lead Time.
   5. Options: Enable the following toggles:
      • Shading
      • Grid lines
      • Trend line
7. Click Save to save the tile config.

Was this article helpful?