Planned stories from devops

Youre spot-on in your analysis, Azure DevOps does not store “planned story points” explicitly, but it infers them using a rule like:

If a user story existed (for example was created) before the sprint started, and is assigned to that sprint (regardless of when it’s closed), then it’s considered planned for that sprint.

But the tricky part you observed is DevOps counts story points as planned in multiple sprints if the same work item is rolled over (moved) to the next sprint(s).

This means for Work Item ID 378, which was created before sprint 2 started, and appears in both sprint 2 and 3, DevOps considers it planned in both.

Let’s say you have a table like the one in your image with, your data model should have one row per [Work Item ID + Sprint] as in your screenshot. This is good.

Create a calculated column or measure for planned story points.

Planned Story Points =
VAR ThisWorkItem = [Work item id]
VAR ThisSprintStart = [sprint start date]
VAR ThisSprint = [sprint]

-- Was this work item created before this sprint?
VAR IsCreatedBeforeSprint = [created date] <= ThisSprintStart

-- Was this item assigned to any earlier sprint?
VAR WasAssignedToEarlierSprint =
CALCULATE(
COUNTROWS(YourTable),
FILTER(
YourTable,
YourTable[Work item id] = ThisWorkItem &&
YourTable[sprint] < ThisSprint
)
) > 0

RETURN
IF(IsCreatedBeforeSprint || WasAssignedToEarlierSprint, [story points], BLANK())

If you need to make sure each work item’s story points are only counted once per sprint, make sure you don’t double-count in your total.

For a measure, you can sum the custom column:

Total Planned Story Points =
SUMX(
YourTable,
[Planned Story Points]
)

Take the Work Item 378:
- Created on 1/18/2025
- Assigned to:
- Sprint 2 (starts 2/3/2025): created before start → planned
- Sprint 3 (starts 2/25/2025): already assigned to earlier sprint → planned

So it should count 8 story points in both sprint 2 and sprint 3