How to Find Remaining Work from Azure DevOps Data

[prabhatnath]

Hi Friends,
What is the best way to find the Remaining Work from Azure DevOps data in Power BI?

If we are using oData (https://analytics.dev.azure.com/{Org}/{Proj}/_odata/v3.0-preview/WorkItems) then we will get columns like WorkItemId, CreatedDate, ChangedDate, State, ClosedDate, OriginalEstimate, RemainingWork, CompletedWork etc. one row per work item. I think this is the best way to get data, but how do we calculate the Remaining Work for Each day of the Sprint?

If we are using Analytics View (current only) then also we will get above columns one row per work item. This is also same issue as above.

If we are using Analytics View (rolling period) then we get multiple rows per work item with the IsCurrent flag and a Date Column that shows the Change Date as below. 

Please suggest what will be the suggested way for the Remaining Work?

Thanks,

Prabhat

[ferryv · Accepted solution]

In Azure DevOps, you should be able to retrieve the iteration start and end date. From this you can define what the current iteration is as the current iteration is essentially the iteration where the current date is between the iteration start date and iteration end date. 

The total work outstanding for the current sprint would be all the items in the current iteration that are not completed. 

I these articles:

• https://www.linkedin.com/pulse/sprint-burndown-charts-powerbi-ferry-van-der-vorst/?trackingId=o5Ze1A...
• https://www.linkedin.com/pulse/sprint-burndown-charts-powerbi-part-2-ferry-van-der-vorst/?trackingId...

I described how to create a velocity chart, which shows you the work completed per day in an iteration.

Hope this helps.