Sample - Release Burndown

Azure DevOps Services | Azure DevOps Server 2019

This article shows you how to display the burndown of User Stories for a release based on work items tagged with a release tag. The following image shows a burndown both by Story Points and User Stories count.

Sample - Release Burndown - Report

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/WorkItemSnapshot?"
        &"$apply=filter(WorkItemType eq 'User Story' "
            &"and StateCategory ne 'Completed' "
            &"and startswith(Area/AreaPath,'{areapath}') "
            &"and Tags/any(x:x/TagName eq '{tagname}') "
            &"and DateValue ge {startdate} "
            &"and DateValue le {enddate} "
        &") "
        &"/groupby ( "
            &"(DateValue,State,Area/AreaPath), "
            &"aggregate ($count as Count, StoryPoints with sum as TotalStoryPoints) "
        &") "
    ,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. Do not 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
  • {tag} - A tag that represents your release. All work items tagged with {tagname} are included in the report
  • {startdate} - The date to start the burndown report
  • {enddate} - The date to end the burndown report.

Query breakdown

The following table describes each part of the query.

Query partDescription
$apply=filter(WorkItemType eq 'User Story'Include User Stories in burndown.
and StateCategory ne 'Completed'Filters out items that are completed. For more information on State Categories see How workflow states and state categories are used in Backlogs and Boards.
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})'"
and Tags/any(x:x/TagName eq '{tagname}').Specifies the Tag that represents the Release to burndown, and to include all work items tagged with {tagname} in the report.
and DateValue ge {startdate}Start burndown on or after the specified date. Example: 2019-04-01Zrepresents 2019-April-01.
and DateValue le {enddate}Start burndown on or before the specified date. Same format as {startdate}.
)Close filter().
/groupby (Start groupby().
(DateValue, State, Area/AreaPath), Group by DateValue (used for trending), and any fields you want to report on.
aggregate ($count as Count, StoryPoints with sum as TotalStoryPoints)Aggregate by count of user stories, and sum of Story Points.
)Close groupby().

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.

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.

Sample -Release Burndown - Fields

For a simple report, perform the following steps:

  1. Select Power BI Visualization Clustered column chart.
  2. Add the field "DateValue" to Axis
    • Right click "DateValue" and select "DateValue", rather than Date Hierarchy
  3. Add the field "TotalStoryPoints" to Values
  4. Add the field "Count" to Values

The example report displays burndown on both Story Points and Count of Stories.

Sample - Release Burndown - Report

To pivot burndown by Area Path, perform the following steps:

  1. Select Power BI Visualization Stacked barchart.
  2. Add the field "DateValue" to Axis.
    • Right click "DateValue" and select "DateValue", rather than Date Hierarchy.
  3. Add the field "TotalStoryPoints" or "Count" to Values. You cannot have 2 fields in Values.
  4. Add the field "Area.AreaPath" to Legend.

The example report displays burndown pivoted by Area Path.

Sample - Release Burndown - Report

To pivot the burndown by State, add the field "State" to Values, replacing "Area.AreaPath".

Sample - Release Burndown - Report

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 using the same steps defined previously in this article.

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/WorkItemSnapshot?"
        &"$apply=filter(WorkItemType eq 'User Story' "
            &"and StateCategory ne 'Completed' "
            &"and (Teams/any(x:x/TeamName eq '{teamname}) or Teams/any(x:x/TeamName eq '{teamname}) or Teams/any(x:x/TeamName eq '{teamname}) "
            &"and Tags/any(x:x/TagName eq '{tagname}') "
            &"and DateValue ge {startdate} "
            &"and DateValue le {enddate} "
        &") "
        &"/groupby ( "
            &"(DateValue,State,Area/AreaPath), "
            &"aggregate ($count as Count, StoryPoints with sum as TotalStoryPoints) "
        &") "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

Burndown with a snapshot every Friday

Using a weekly snapshot reduces the amount of data pulled into Power BI, and increases query performance.

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/WorkItemSnapshot?"
        &"$apply=filter(WorkItemType eq 'User Story' "
            &"and StateCategory ne 'Completed' "
            &"and startswith(Area/AreaPath,'{areapath}') "
            &"and Tags/any(x:x/TagName eq '{tagname}') "
            &"and DateValue ge {startdate} "
            &"and DateValue le {enddate} "
            &"and Date/DayName eq 'Friday' "
        &") "
        &"/groupby ( "
            &"(DateValue,State,Area/AreaPath,AreaSK), "
            &"aggregate ($count as Count, StoryPoints with sum as TotalStoryPoints) "
        &") "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

Burndown based off an Iteration Path

Some organizations use Iteration Paths to mark Stories for a Release. For example, they may have an Iteration Path of MyProject\Release1. The following query shows how to select Stories by Iteration 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/WorkItemSnapshot?"
        &"$apply=filter(WorkItemType eq 'User Story' "
            &"and StateCategory ne 'Completed' "
            &"and startswith(Area/AreaPath,'{areapath}') "
            &"and startswith(Iteration/IterationPath,'{iterationpath}') "
            &"and DateValue ge {startdate} "
            &"and DateValue le {enddate} "
            &") "
        &"/groupby ( "
            &"(DateValue,StateCategory,State,Area/AreaPath,AreaSK), "
            &"aggregate ($count as Count, StoryPoints with sum as TotalStoryPoints) "
        &") "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

Burndown based off a custom field

Some organizations use a custom field to mark Stories for a Release. For example, they may have a field called "Milestone". This query shows you how to select Stories by a custom field. You will need to replace both {customfield} and {releasevalue} in the query. To determine the name of your custom field, explore the Analytics metadata. You will use the Property Name as {customfield}.

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/WorkItemSnapshot?"
        &"$apply=filter(WorkItemType eq 'User Story' "
            &"and StateCategory ne 'Completed' "
            &"and startswith(Area/AreaPath,'{areapath}') "
            &"and {customfieldname} eq '{releasevalue}' "
            &"and DateValue ge {startdate} "
            &"and DateValue le {enddate} "
            &") "
        &"/groupby ( "
            &"(DateValue,StateCategory,State,Area/AreaPath,AreaSK), "
            &"aggregate ($count as Count, StoryPoints with sum as TotalStoryPoints) "
        &") "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

Full list of sample reports