Velocity chart similar to Azure Devops

[SureshA]

I am trying to build a velocity chart similar to Azure Devops to depict for each sprint

1. Planned

2. Completed 

3. Removed

I have used ado analytic views with history 

I am unable to get the planned story points in powerbi to match with the velocity chart of ado

Ado calculate differently for the planned story points based on snapshot view. How to replicate the planned story points 

Completed and removed are straight forward based on work item state at the end of spirint

[DataNinja777]

Hi @SureshA ,

To replicate the Azure DevOps velocity chart in Power BI, particularly the "Planned" story points, you must align with the platform's snapshot-based calculation. "Planned" work in Azure DevOps represents all work items assigned to a sprint before its official start date. To capture this, connect Power BI to an Azure DevOps Analytics view configured with daily historical granularity. This provides a daily snapshot of work item states, which is essential for accurate historical reporting.

For calculating completed work, you can sum the effort for items in a terminal state at the end of the sprint.

Completed Story Points = 
CALCULATE(
    SUM('YourTableName'[Story Points]),
    'YourTableName'[State] IN {"Done", "Closed"}
)

Removed work includes items that were part of the sprint at some point but were moved to a different iteration before the sprint ended. Accurately tracking this often involves analyzing changes to the Iteration Path field over the sprint's duration.

The crucial part is calculating the "Planned" story points, which requires querying the historical data for the state of the backlog on the sprint's start date. Assuming you have a WorkItemBoardSnapshot table from your Analytics View and a related 'Sprints' table with start dates, the DAX measure would isolate the work items assigned to the specific sprint on that initial day.

Planned Story Points =
VAR CurrentSprintStartDate = MIN('Sprints'[Start Date])
VAR CurrentSprintPath = SELECTEDVALUE('Sprints'[Iteration Path])
RETURN
CALCULATE(
    SUM('WorkItemBoardSnapshot'[Story Points]),
    FILTER(
        ALL('WorkItemBoardSnapshot'),
        'WorkItemBoardSnapshot'[Date] = CurrentSprintStartDate &&
        'WorkItemBoardSnapshot'[Iteration Path] = CurrentSprintPath
    )
)

This approach ensures your Power BI report mirrors the Azure DevOps methodology by summing the story points of work items that were planned for the sprint at the moment it began, providing an accurate baseline for your velocity calculation. These measures can then be used in a stacked column chart with the sprint on the axis to visualize the planned, completed, and removed work over time.

Best regards,

[SureshA]

Thanks 

I have the following fields with all history from ado analytics view filtered for my required squads

1. Work item type

2. Iteration path

3. Interation start date

4.. Iteration end date

5. Created date

6. Changed Date

7. Story points

In your dax measure for planned story points, you have referred 

<<WorkItemBoardSnapshot table from your Analytics View and a related 'Sprints' table with start dates>>

• I have only one table which has history along with the above listed fields containing sprint start date i.e iteration start datehow do I use this in the Dax
• secondly ado also considers story points that got planned during sprint progress. How do I sum up these with the created and change date

[v-hjannapu]

Hi @SureshA,
Thank you for your followup.

It shows how to load your data, create DAX measures using your existing columns like Iteration Start Date, Changed Date, Created Date, and State, and finally build a stacked column chart.

I tested it with my sample data, and it worked fine. Please find the attached screenshot and Pbix for your reference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Harshitha.

[SureshA]

Thanks for your revert. Pls can you provide the dax I can't download the pbix in my official env

[v-hjannapu]

Hi @SureshA,
Thank you for getting back to me.
I will share the DAX formula directly here so you can implement it in your report:

Added During Sprint = 
VAR SprintStart = MIN('Planned Story Points'[Iteration Start Date])
VAR SprintEnd = MIN('Planned Story Points'[Iteration End Date])
VAR SprintPath = SELECTEDVALUE('Planned Story Points'[Iteration Path])
RETURN
CALCULATE(
    SUM('Planned Story Points'[Story Points]),
    FILTER(
        'Planned Story Points',
        'Planned Story Points'[Iteration Path] = SprintPath &&
        'Planned Story Points'[Changed Date] > SprintStart &&
        'Planned Story Points'[Changed Date] <= SprintEnd
    )
)

Completed Story Points = 
VAR SprintPath = SELECTEDVALUE('Planned Story Points'[Iteration Path])
RETURN
CALCULATE(
    SUM('Planned Story Points'[Story Points]),
    FILTER(
        'Planned Story Points',
        'Planned Story Points'[Iteration Path] = SprintPath &&
        'Planned Story Points'[State] IN { "Done", "Closed" }
    )
)

Planned Story Points = 
VAR SprintStart = MIN('Planned Story Points'[Iteration Start Date])
VAR SprintPath = SELECTEDVALUE('Planned Story Points'[Iteration Path])
RETURN
CALCULATE(
    SUM('Planned Story Points'[Story Points]),
    FILTER(
        'Planned Story Points',
        'Planned Story Points'[Iteration Path] = SprintPath &&
        'Planned Story Points'[Changed Date] <= SprintStart
    )
)

If you face any challenges applying this, feel free to share more details, and I will be happy to guide you step by step. 
Best regards,
Harshitha.
Microsoft Fabric Community Support.

[SureshA]

Hi @v-hjannapu thanks for sharing dax formulas. I have used in my data set to depict then them in stack column chart.

• I am able to get more story points, since the computation is done on all rows. I have used history in my ado analytics view hence my dataset has multiple rows for each work item. I need to get the latest update done on the work item to compute planned story points