Planned stories from devops

[s_fs]

Hi, I have created a report getting data from devops board. As we know we have user story and story points, sprint start date end date these and all. I am comparing how many planned and how much completed story points for each sprint. I have created analytic view where I can get data. But for planned story points there is no such field to add in analytics view. I believe devops board is applying some logic where your user story is created before sprint that will consider as planned. I tried to implement same logic in power bi,if created date of work id is before sprint start date take story points other wise blank, it is giving data. But there are few user stories that are planned for sprint 1 and they didn't completed that and again move to sprint 2 . So, devops board considering it as planned for both sprints. But when I fetch the same user story from anlytic view as usal it is closed in sprint 2 .so I can see that work id in sprint 2 and logic applying to sprint 2 only. As you can see from my screen shot work item id 378 is planned for sprint 2 and 3 so the my custom column should take the story points in sprint 2and 3 as well. 

Is there a way that i can implement logic behind planned story points in devops and implement it in power bi. 

[v-sgandrathi]

Hi @s_fs,

Thanks for using Microsoft Fabric Community Forum.

Thank you for your detailed explanation! I’d like to confirm that @AmiraBedh has provided an prompt response. Their approach accurately reflects how Azure DevOps calculates Planned Story Points.

DevOps does not explicitly store Planned Story Points but derives them based on work item creation and sprint assignment.
A work item is considered planned if it existed before the sprint started and was assigned to that sprint, even if it was later moved to a different sprint.
The DAX formula shared ensures that work items are counted as planned in multiple sprints if they were carried over, accurately reflecting DevOps behavior.
To avoid double counting, make sure your total planned story points calculation correctly aggregates values per sprint.

This method should assist you in aligning your Power BI calculations with DevOps. Please feel free to reach out if you need any help.

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!

Regards,
Sahasra.

[v-sgandrathi]

Hi @s_fs,

we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If our response addressed by the community member for  your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[v-sgandrathi]

Hi @s_fs,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Thank you.

[v-sgandrathi]

Hi @s_fs,

I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.

If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you.

[AmiraBedh · Accepted solution]

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

[s_fs]

Hi, Thanks for the response

From the above logic, you are checking that work item id is assigned to before sprint or not. But if it satisfies it is doing a count. But when it satisfies it should add the story points to sprint 1 also right. Means 378 id has 8 story points in sprint 3 and planned for sprint 2 also. Then planned story point column will provide 8 value in both sprint 2 and 3 rows. That is not happening here.