Velocity chart similar to Azure Devops

Hi SureshA ,

To replicate the Azure DevOps velocity chart in Power BI, particularly the "Planned" story points, you must align with the platform's snapshot-based calculation. "Planned" work in Azure DevOps represents all work items assigned to a sprint before its official start date. To capture this, connect Power BI to an Azure DevOps Analytics view configured with daily historical granularity. This provides a daily snapshot of work item states, which is essential for accurate historical reporting.

For calculating completed work, you can sum the effort for items in a terminal state at the end of the sprint.

Completed Story Points = 
CALCULATE(
    SUM('YourTableName'[Story Points]),
    'YourTableName'[State] IN {"Done", "Closed"}
)

Removed work includes items that were part of the sprint at some point but were moved to a different iteration before the sprint ended. Accurately tracking this often involves analyzing changes to the Iteration Path field over the sprint's duration.

The crucial part is calculating the "Planned" story points, which requires querying the historical data for the state of the backlog on the sprint's start date. Assuming you have a WorkItemBoardSnapshot table from your Analytics View and a related 'Sprints' table with start dates, the DAX measure would isolate the work items assigned to the specific sprint on that initial day.

Planned Story Points =
VAR CurrentSprintStartDate = MIN('Sprints'[Start Date])
VAR CurrentSprintPath = SELECTEDVALUE('Sprints'[Iteration Path])
RETURN
CALCULATE(
    SUM('WorkItemBoardSnapshot'[Story Points]),
    FILTER(
        ALL('WorkItemBoardSnapshot'),
        'WorkItemBoardSnapshot'[Date] = CurrentSprintStartDate &&
        'WorkItemBoardSnapshot'[Iteration Path] = CurrentSprintPath
    )
)

This approach ensures your Power BI report mirrors the Azure DevOps methodology by summing the story points of work items that were planned for the sprint at the moment it began, providing an accurate baseline for your velocity calculation. These measures can then be used in a stacked column chart with the sprint on the axis to visualize the planned, completed, and removed work over time.

Best regards,

Anonymous i have tried the dax it doesnt match with ADO velocity chart

SureshA Hey,
Try below steps as per my understanding -  

Use the current or final state at sprint end.

Completed = sum of story points for items in Done/Completed state at sprint end.

Removed = sum of story points for items moved to Removed/Removed state by sprint end.

PlannedPoints =
CALCULATE(
SUM(SnapshotTable[StoryPoints]),
SnapshotTable[Iteration] = SelectedSprint,
SnapshotTable[SnapshotDate] = SprintStartDate
)

Thanks

Harish KM

Please accept this as a solution if this solves your problem and give kudos as well.