Azure DevOps OData Sprint Burndown Chart not showing 0 story point

Question

I am referring to this MS docs when building Power BI report using OData query.

I have slightly modified the query to display Sprint Burndown, sprint-by-sprint and it all works fine until I realize when the team has completed all story points, by right the burndown area should go all the way to zero like we can see in Sprint > Analytics in Azure DevOps UI, but in my OData query, the last day of the data will not shown because it has no story point, resulting my visualization will never go to zero (but in actual fact it does).

You might ask why am I doing customized Power BI report, I am doing this visualization across multiple organizations that's why we want to have an overall view of all of them in single Power BI report.

My OData query

let
   Source = OData.Feed ("https://analytics.dev.azure.com/"& Organization &"/_odata/v3.0-preview/WorkItemSnapshot? "
        &"$apply=filter( "
            &"Project/ProjectName eq '{ProjectName}' "            
            &"and WorkItemType eq 'User Story' "
            &"and StateCategory ne 'Completed' "
            &"and DateValue ge Iteration/StartDate "
            &"and DateValue le Iteration/EndDate "
            &"and Iteration/StartDate ge 2020-01-01Z "
        &") "
 &" &$expand=Project,Iteration,Teams"
        ),
    #"Expanded Project" = Table.ExpandRecordColumn(Source, "Project", {"ProjectName"}, {"Project.ProjectName"}),
    #"Expanded Iteration" = Table.ExpandRecordColumn(#"Expanded Project", "Iteration", {"IterationName", "IterationPath", "StartDate", "EndDate"}, {"Iteration.IterationName", "Iteration.IterationPath", "Iteration.StartDate",  "Iteration.EndDate"}),
    #"Expanded Teams" = Table.ExpandTableColumn(#"Expanded Iteration", "Teams", {"TeamName"}, {"Teams.TeamName"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Teams",{"WorkItemId", "CompletedDate", "StoryPoints", "Teams.TeamName", "Project.ProjectName", "Iteration.IterationName", "Iteration.IterationPath", "Iteration.StartDate",  "Iteration.EndDate", "DateValue"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Teams.TeamName", "Project.ProjectName", "Iteration.IterationName", "Iteration.IterationPath", "Iteration.StartDate", "Iteration.EndDate", "DateValue"}, {{"Total Story Points", each List.Sum([StoryPoints]), type number}})
in
    #"Grouped Rows"

It might not look as elegant as you can see I am not doing server side groupby, but I want to have teamName information as well, I tried expanding and groupby in query but keep getting error.

Also I know the reason of not having the 0 story point is because of the StateCategory ne 'Completed' ". How do I properly visualize Azure DevOps Burndown chart in Power BI?

p/s: It would be better if anyone can suggest on visualizing the total scope and ideal trend as well.

HHS HHS · Accepted Answer · 2020-05-22T07:22:52

The following query will work for calculating the total scope

https://analytics.dev.azure.com/{orgName}/{ProjectName}/_odata/v3.0-preview/WorkItemSnapshot?
        $apply=filter(
        Teams/any(d:d/TeamName eq '{TeamName}')
        and Iteration/IterationName eq '{IterationName}'
        and (WorkItemType eq 'User Story') 
        and StateCategory ne null 
        and (DateValue ge Iteration/StartDate 
            and DateValue le Iteration/EndDate))
        /groupby((DateSK),aggregate($count as TotalScope))

Howerever you will have to iterate throught each team for this query , I couldnot find any way to group the results based on teams. You can get all the teams using the Teams Rest API. Hope this helps. :)

Azure DevOps OData Sprint Burndown Chart not showing 0 story point

1 Answers