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

[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

[SureshAnanth]

@v-hjannapu hope you had chance to check

[v-hjannapu]

Hi @SureshA ,
Thank you for your response. As you mentioned, since your dataset includes history rows, it is important to select only the latest values before the sprint start or end to align with the Azure DevOps velocity chart.

For Planned, count story points from the final snapshot taken before the sprint start, excluding any items created or updated afterwards.
For Completed, use story points for items that were Done or closed on or before the sprint end, referencing their most recent state prior to sprint end.
For Removed, include work items that were initially in the sprint but moved out before the sprint concluded.

I have updated the DAX formulas for Completed and Removed story points to eliminate duplicates and reflect this approach. You can now use these formulas directly in your model.

DAX for Completed Story Points:

Completed Story Points =
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
SUMX(
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE(
                'Planned Story Points',
                'Planned Story Points'[Work Item ID],
                "LatestChangeBeforeEnd",
                CALCULATE(
                    MAX('Planned Story Points'[Changed Date]),
                    'Planned Story Points'[Changed Date] <= SprintEnd &&
                    'Planned Story Points'[Iteration Path] = SprintPath
                )
            ),
            "StoryPointsIfCompleted",
            CALCULATE(
                MAX('Planned Story Points'[Story Points]),
                'Planned Story Points'[Changed Date] <= SprintEnd &&
                'Planned Story Points'[Iteration Path] = SprintPath &&
                'Planned Story Points'[State] IN { "Done", "Closed" }
            )
        ),
        NOT(ISBLANK([StoryPointsIfCompleted]))
    ),
    [StoryPointsIfCompleted]
)

DAX for Removed Story Points:

Removed Story Points =
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
SUMX(
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE(
                'Planned Story Points',
                'Planned Story Points'[Work Item ID],
                "RemovedDate",
                CALCULATE(
                    MAX('Planned Story Points'[Changed Date]),
                    'Planned Story Points'[Changed Date] <= SprintEnd &&
                    'Planned Story Points'[Iteration Path] <> SprintPath
                )
            ),
            "StoryPointsRemoved",
            CALCULATE(
                MAX('Planned Story Points'[Story Points]),
                'Planned Story Points'[Changed Date] <= SprintEnd
            )
        ),
        NOT(ISBLANK([StoryPointsRemoved]))
    ),
    [StoryPointsRemoved]
)

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated.
Best Regards,
Harshitha.

[SureshAnanth]

@v-hjannapu Thanks I am able to get the completed story points matching with ADO velocity chart removing the duplicate. Further this is getting computed only for 1 sprint for subsequent sprints i dont see the value displayed in the chart

However, the planned story points is not matching i have compared

for past sprint 215 in DAX vs ADO velcity chart shows 126 with a diff of 89

For next sprint the DAX 155 vs ADO 105 with a diff of 50

Further can you share the DAX to fix the planned story points and also to get the completed for subsequent sprints

Further it is good to depict Completed Late and Incomplete to replicate similar to ADO velocity chart

Kindly help if you can share the DAX for these considering history

[SureshAnanth]

@v-hjannapu Hope you have got some feedback

[v-hjannapu]

Hi @SureshAnanth ,
Thanks for followup 
Here the Dax you can try:

Planned Story Points =
VAR SprintStart = MIN('Planned Story Points'[Iteration Start Date])
VAR SprintPath = SELECTEDVALUE('Planned Story Points'[Iteration Path])
RETURN
SUMX(
    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
            )
        ),
        "StoryPointsAtStart",
        CALCULATE(
            MAX('Planned Story Points'[Story Points]),
            'Planned Story Points'[Changed Date] = [LatestBeforeSprint] &&
            'Planned Story Points'[Iteration Path] = SprintPath
        )
    ),
    [StoryPointsAtStart]
)

Completed Story Points =
VAR SprintEnd = MIN('Planned Story Points'[Iteration End Date])
VAR SprintPath = SELECTEDVALUE('Planned Story Points'[Iteration Path])
RETURN
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            'Planned Story Points',
            'Planned Story Points'[Work Item ID],
            "LatestBeforeEnd",
            CALCULATE(
                MAX('Planned Story Points'[Changed Date]),
                'Planned Story Points'[Changed Date] <= SprintEnd &&
                'Planned Story Points'[Iteration Path] = SprintPath
            )
        ),
        "StoryPointsIfDone",
        CALCULATE(
            MAX('Planned Story Points'[Story Points]),
            'Planned Story Points'[Changed Date] = [LatestBeforeEnd] &&
            'Planned Story Points'[State] IN { "Done", "Closed" }
        )
    ),
    [StoryPointsIfDone]
)

Completed Late Story Points =
VAR SprintEnd = MIN('Planned Story Points'[Iteration End Date])
VAR SprintPath = SELECTEDVALUE('Planned Story Points'[Iteration Path])
RETURN
SUMX(
    FILTER(
        'Planned Story Points',
        'Planned Story Points'[Iteration Path] = SprintPath &&
        'Planned Story Points'[State] IN { "Done", "Closed" } &&
        'Planned Story Points'[Changed Date] > SprintEnd
    ),
    'Planned Story Points'[Story Points]
)

Incomplete Story Points =
VAR SprintEnd = MIN('Planned Story Points'[Iteration End Date])
VAR SprintPath = SELECTEDVALUE('Planned Story Points'[Iteration Path])
RETURN
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            'Planned Story Points',
            'Planned Story Points'[Work Item ID],
            "LatestBeforeEnd",
            CALCULATE(
                MAX('Planned Story Points'[Changed Date]),
                'Planned Story Points'[Changed Date] <= SprintEnd &&
                'Planned Story Points'[Iteration Path] = SprintPath
            )
        ),
        "StoryPointsIfIncomplete",
        CALCULATE(
            MAX('Planned Story Points'[Story Points]),
            'Planned Story Points'[Changed Date] = [LatestBeforeEnd] &&
            NOT('Planned Story Points'[State] IN { "Done", "Closed" })
        )
    ),
    [StoryPointsIfIncomplete]
)

After trying this if you want any help let me know I will be happy to support you further if needed.
Please Accept as solution if this meets your needs and a Kudos would be appreciated.

Reagrds,
Harshitha.

[v-hjannapu]

Hi @SureshA,

Just wanted to check if you had the opportunity to review the suggestion provided?

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

Thank You.

Harshitha.

[v-hjannapu]

Hi @SureshA,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution so that other community members can find it easily.

Thank you.

[SureshA]

@v-hjannapu thanks. Will this dax match with the output of ado velocity chart,

Considering the history rows used in powerbi

Further planned story i believe ado velocity chart calculates 

1. Assigned story points before iteration start

2. Story points that gets changed less than equal to iteration end date

Pls confirm if you have compared the dax to match ado velocity chart

[v-hjannapu]

Hi @SureshA,
Thanks for followup

Yes, the DAX I shared is made to match how ADO velocity chart calculates planned story points. It adds up story points for work items that were already assigned to the sprint before the sprint started. It picks the latest story points value from history before sprint start for each work item.

 Any work items added after the sprint started or any changes to story points after the sprint started will not be included in the planned points. These would come under added or completed points. I have not done a side-by-side match of this DAX with your exact ADO velocity chart output, but the logic is based on how ADO calculates planned points  taking a snapshot at sprint start.

If you still see a difference or have an example where it doesn’t match, please share. I will be happy to help you further and adjust the DAX if needed.

Regards,
Harshitha.