Forum Discussion

SureshA's avatar
SureshA
Icon for Helper II rankHelper II
1 year ago
Solved

Velocity chart similar to Azure Devops

I am trying to build a velocity chart similar to Azure Devops to depict for each sprint 1. Planned 2. Completed  3. Removed   I have used ado analytic views with history  I am unable to get the...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi SureshA ,
    we get that you cant share the official data but it is working as expected with our sample data. could you please explain more on mismatch so we can try changing Dax,find the attached file for your reference.

    Regards,
    Harshitha.
     

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi SureshA,
Thank you for getting back to me.
I will share the DAX formula directly here so you can implement it in your report:

Added During Sprint = 
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
CALCULATE(
    SUM('Planned Story Points'[Story Points]),
    FILTER(
        'Planned Story Points',
        'Planned Story Points'[Iteration Path] = SprintPath &&
        'Planned Story Points'[Changed Date] > SprintStart &&
        'Planned Story Points'[Changed Date] <= SprintEnd
    )
)

 

Completed Story Points = 
VAR SprintPath = SELECTEDVALUE('Planned Story Points'[Iteration Path])
RETURN
CALCULATE(
    SUM('Planned Story Points'[Story Points]),
    FILTER(
        'Planned Story Points',
        'Planned Story Points'[Iteration Path] = SprintPath &&
        'Planned Story Points'[State] IN { "Done", "Closed" }
    )
)

 

Planned Story Points = 
VAR SprintStart = MIN('Planned Story Points'[Iteration Start Date])
VAR SprintPath = SELECTEDVALUE('Planned Story Points'[Iteration Path])
RETURN
CALCULATE(
    SUM('Planned Story Points'[Story Points]),
    FILTER(
        'Planned Story Points',
        'Planned Story Points'[Iteration Path] = SprintPath &&
        'Planned Story Points'[Changed Date] <= SprintStart
    )
)


If you face any challenges applying this, feel free to share more details, and I will be happy to guide you step by step. 
Best regards,
Harshitha.
Microsoft Fabric Community Support.

SureshA's avatar
SureshA
Icon for Helper II rankHelper II
1 year ago

Hi Anonymous thanks for sharing dax formulas. I have used in my data set to depict then them in stack column chart.

  • I am able to get more story points, since the computation is done on all rows. I have used history in my ado analytics view hence my dataset has multiple rows for each work item. I need to get the latest update done on the work item to compute planned story points
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi SureshA,
    Thanks  for getting back and explaining clearly.
    This formula looks at each work item, takes only the last update just before the sprint started, and adds up the story points from that  so that the same work item is not counted more than once.
    updated DAX you can use:

    Planned Story Points =
VAR SprintStart = MIN('Planned Story Points'[Iteration Start Date])
VAR SprintPath = SELECTEDVALUE('Planned Story Points'[Iteration Path])

RETURN
CALCULATE(
    SUMX(
        FILTER(
            ADDCOLUMNS(
                SUMMARIZE(
                    'Planned Story Points',
                    'Planned Story Points'[Work Item ID],
                    "LatestChangeBeforeSprint",
                    CALCULATE(
                        MAX('Planned Story Points'[Changed Date]),
                        'Planned Story Points'[Changed Date] <= SprintStart
                    )
                ),
                "StoryPoints",
                CALCULATE(
                    MAX('Planned Story Points'[Story Points]),
                    'Planned Story Points'[Changed Date] <= SprintStart
                )
            ),
            NOT(ISBLANK([StoryPoints]))
        ),
        [StoryPoints]
    ),
    'Planned Story Points'[Iteration Path] = SprintPath
)

     

    If this helped, please mark the answer as solution and give a Kudos so it helps others too .

    Best regards,
    Harshitha.
    Microsoft Fabric Community Support.

    • SureshA's avatar
      SureshA
      Icon for Helper II rankHelper II
      1 year ago

      Anonymous Thanks. What about the below edge cases

      1. New stories with story points that gets added after sprint start

      2. Existing stories that gets modified after sprint start

      In summary the dax for planned story points should exactly replicate the velocity chart that is in built as a gadget in azure devops

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi SureshA,

        To make sure the Planned Story Points match exactly how Azure DevOps calculates them, we need to take care of two important things. First, if any new user stories were added after the sprint started, we should not count them in the planned total, because they were not part of the original sprint plan. Second, if existing stories had their story points changed after the sprint began, we should ignore those updates and only consider the story points value as it was before the sprint started. 
        To handle both these cases, I have updated the DAX for Planned Story Points.

        Planned Story Points =
VAR SprintStart = MIN('Planned Story Points'[Iteration Start Date])
VAR SprintPath = SELECTEDVALUE('Planned Story Points'[Iteration Path])

RETURN
SUMX(
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE(
                'Planned Story Points',
                'Planned Story Points'[Work Item ID],
                "LatestBeforeSprint",
                CALCULATE(
                    MAX('Planned Story Points'[Changed Date]),
                    'Planned Story Points'[Changed Date] <= SprintStart &&
                    'Planned Story Points'[Iteration Path] = SprintPath
                )
            ),
            "StoryPointsAtSprintStart",
            CALCULATE(
                MAX('Planned Story Points'[Story Points]),
                'Planned Story Points'[Changed Date] <= SprintStart &&
                'Planned Story Points'[Iteration Path] = SprintPath
            ),
            "CreatedDate",
            CALCULATE(
                MIN('Planned Story Points'[Created Date])
            )
        ),
        [CreatedDate] <= SprintStart &&
        NOT(ISBLANK([StoryPointsAtSprintStart]))
    ),
    [StoryPointsAtSprintStart]
)


        Regards,
        Community support Team.