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

[HarishKM]

@SureshA Hey,
Try below steps as per my understanding -  

Use the current or final state at sprint end.

Completed = sum of story points for items in Done/Completed state at sprint end.

Removed = sum of story points for items moved to Removed/Removed state by sprint end.

PlannedPoints =
CALCULATE(
SUM(SnapshotTable[StoryPoints]),
SnapshotTable[Iteration] = SelectedSprint,
SnapshotTable[SnapshotDate] = SprintStartDate
)

Thanks

Harish KM

Please accept this as a solution if this solves your problem and give kudos as well.

[SureshAnanth]

@v-hjannapu i have tried the dax it doesnt match with ADO velocity chart

[v-hjannapu · Accepted solution]

Hi @SureshA ,
we get that you cant share the official data but it is working as expected with our sample data. could you please explain more on mismatch so we can try changing Dax,find the attached file for your reference.

Regards,
Harshitha.
 

[SureshA]

@v-hjannapu thanks unfortunately I can't open the pbix file in my office environments. Can you please share the dax

[v-hjannapu]

Hi  @SureshA ,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

[SureshA]

@v-hjannapu I m unable to get any data from official network. Kindly try out the dax and verify its output against existing ado velocity chart of your squad 

[SureshA]

@v-hjannapu do you have any update 

[v-hjannapu]

Hi @SureshA,
I have already provided the solution on Tuesday, July 8th. Could you please check my earlier response along with the attached file.

Regards,
Harshitha.

[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

[v-hjannapu]

Hi @SureshA,
Thanks  for getting back and explaining clearly.
This formula looks at each work item, takes only the last update just before the sprint started, and adds up the story points from that  so that the same work item is not counted more than once.
updated DAX you can use:

Planned Story Points =
VAR SprintStart = MIN('Planned Story Points'[Iteration Start Date])
VAR SprintPath = SELECTEDVALUE('Planned Story Points'[Iteration Path])

RETURN
CALCULATE(
    SUMX(
        FILTER(
            ADDCOLUMNS(
                SUMMARIZE(
                    'Planned Story Points',
                    'Planned Story Points'[Work Item ID],
                    "LatestChangeBeforeSprint",
                    CALCULATE(
                        MAX('Planned Story Points'[Changed Date]),
                        'Planned Story Points'[Changed Date] <= SprintStart
                    )
                ),
                "StoryPoints",
                CALCULATE(
                    MAX('Planned Story Points'[Story Points]),
                    'Planned Story Points'[Changed Date] <= SprintStart
                )
            ),
            NOT(ISBLANK([StoryPoints]))
        ),
        [StoryPoints]
    ),
    'Planned Story Points'[Iteration Path] = SprintPath
)

If this helped, please mark the answer as solution and give a Kudos so it helps others too .

Best regards,
Harshitha.
Microsoft Fabric Community Support.

[SureshA]

@v-hjannapu Thanks. What about the below edge cases

1. New stories with story points that gets added after sprint start

2. Existing stories that gets modified after sprint start

In summary the dax for planned story points should exactly replicate the velocity chart that is in built as a gadget in azure devops

[v-hjannapu]

Hi @SureshA,

To make sure the Planned Story Points match exactly how Azure DevOps calculates them, we need to take care of two important things. First, if any new user stories were added after the sprint started, we should not count them in the planned total, because they were not part of the original sprint plan. Second, if existing stories had their story points changed after the sprint began, we should ignore those updates and only consider the story points value as it was before the sprint started. 
To handle both these cases, I have updated the DAX for Planned Story Points.

Planned Story Points =
VAR SprintStart = MIN('Planned Story Points'[Iteration Start Date])
VAR SprintPath = SELECTEDVALUE('Planned Story Points'[Iteration Path])

RETURN
SUMX(
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE(
                'Planned Story Points',
                'Planned Story Points'[Work Item ID],
                "LatestBeforeSprint",
                CALCULATE(
                    MAX('Planned Story Points'[Changed Date]),
                    'Planned Story Points'[Changed Date] <= SprintStart &&
                    'Planned Story Points'[Iteration Path] = SprintPath
                )
            ),
            "StoryPointsAtSprintStart",
            CALCULATE(
                MAX('Planned Story Points'[Story Points]),
                'Planned Story Points'[Changed Date] <= SprintStart &&
                'Planned Story Points'[Iteration Path] = SprintPath
            ),
            "CreatedDate",
            CALCULATE(
                MIN('Planned Story Points'[Created Date])
            )
        ),
        [CreatedDate] <= SprintStart &&
        NOT(ISBLANK([StoryPointsAtSprintStart]))
    ),
    [StoryPointsAtSprintStart]
)

Regards,
Community support Team.

[SureshA]

Thanks Let me implement and see if this matches ado velocity chart for planned story points 

[v-hjannapu]

Hi @SureshA,
Thank you so much for your effort and for keeping us informed.  If you face any issues after the update, we are here to help you.

[SureshAnanth]

@v-hjannapu I have used the planned story points DAX, I could see the no of story points have come down. I believe the DAX is working to remove duplicates as the data set has history for each sprint. However there is still difference when compared to velocity chart in ADO

Also,  I would require the DAX for completed story points and removed story points after removing the duplicates and considering the latest state before or on the sprint end date

Based on ADO  View and configure team velocity - Azure DevOps | Microsoft Learn

I could see the below calculation 

a) Planned = Work items assigned to a sprint before it starts. If reassigned after the sprint begins, they remain Planned in the original sprint and appear as Late or Incomplete in the new sprint.

b) Completed =Work items assigned to the sprint and completed before the end of the sprint.

Trust the DAX will reflect this in PowerBI to match ADO velocity considering the analytic views are sourced using history of work items

How to verify the DAX output with ADO velocity based on the source that is pulled in powerbi which has history