Facing Problems with Velocity Calculation

[amit_wairkar]

We are trying to build Azure DevOps Metrics using the Azure DevOps server 2022 in Power BI. We referred the links given by Microsoft and tried to build some metrics but facing issues when trying to create Velocity using Story Points. Incase of a Sprint failure, the associated User Story/ies which is incomplete is taken to the Backlogs and then shifted to another Sprint. The Story Points for the failed Sprint should becomes Zero.

In this case we are not able to identify which User story was shifted from one sprint to another. So, we have a concern regarding calculating the Actual Story Points.

Kindly let us know if there is a better way to build this metrics for Azure DevOps Server.

Thanks in Advance.

Regards,

Amit Wairkar

[Sahir_Maharaj]

Hello @amit_wairkar,

Can you please try this approach:

ActualStoryPoints = 
VAR CurrentSprint = SELECTEDVALUE(Sprints[SprintName])
VAR CompletedStories = 
    CALCULATETABLE(
        VALUES(WorkItems[WorkItemID]),
        WorkItems[State] = "Done",
        WorkItems[SprintName] = CurrentSprint
    )

VAR RolledOverStories = 
    CALCULATETABLE(
        VALUES(WorkItems[WorkItemID]),
        WorkItems[State] <> "Done",
        WorkItems[SprintName] = CurrentSprint
    )

VAR CompletedStoryPoints = 
    CALCULATE(
        SUM(WorkItems[StoryPoints]),
        WorkItems[WorkItemID] IN CompletedStories
    )

RETURN 
    CompletedStoryPoints