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.
