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 ,
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.
 

 

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

Anonymous thanks unfortunately I can't open the pbix file in my office environments. Can you please share the dax

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi SureshA,
    Thanks for your update
    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(
    ADDCOLUMNS(
        SUMMARIZE(
            'Planned Story Points',
            'Planned Story Points'[Work Item ID],
            "LatestBeforeSprint",
            CALCULATE(
                MAX('Planned Story Points'[Changed Date]),
                FILTER(
                    'Planned Story Points',
                    'Planned Story Points'[Changed Date] <= SprintStart &&
                    'Planned Story Points'[Iteration Path] = SprintPath
                )
            )
        ),
        "StoryPointsAtStart",
        CALCULATE(
            MAX('Planned Story Points'[Story Points]),
            FILTER(
                'Planned Story Points',
                'Planned Story Points'[Changed Date] = [LatestBeforeSprint] &&
                'Planned Story Points'[Iteration Path] = SprintPath
            )
        )
    ),
    [StoryPointsAtStart]
)


    Incomplete story points:

    Incomplete Story Points = 
VAR SprintEnd = MIN('Planned Story Points'[Iteration End Date])
VAR SprintPath = SELECTEDVALUE('Planned Story Points'[Iteration Path])

RETURN
SUMX(
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE(
                'Planned Story Points',
                'Planned Story Points'[Work Item ID]
            ),
            "StoryPointsIfIncomplete",
            CALCULATE(
                MAX('Planned Story Points'[Story Points]),
                FILTER(
                    'Planned Story Points',
                    'Planned Story Points'[Changed Date] = 
                        CALCULATE(
                            MAX('Planned Story Points'[Changed Date]),
                            'Planned Story Points'[Changed Date] <= SprintEnd &&
                            'Planned Story Points'[Iteration Path] = SprintPath &&
                            'Planned Story Points'[Work Item ID] = EARLIER('Planned Story Points'[Work Item ID])
                        ) &&
                    'Planned Story Points'[Iteration Path] = SprintPath &&
                    NOT('Planned Story Points'[State] IN { "Done", "Closed" })
                )
            )
        ),
        NOT(ISBLANK([StoryPointsIfIncomplete]))
    ),
    [StoryPointsIfIncomplete]
)

     
    completed story points:

    Completed Story Points = 
VAR SprintEnd = MIN('Planned Story Points'[Iteration End Date])
VAR SprintPath = SELECTEDVALUE('Planned Story Points'[Iteration Path])

RETURN
SUMX(
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE(
                'Planned Story Points',
                'Planned Story Points'[Work Item ID]
            ),
            "LatestBeforeEnd",
            CALCULATE(
                MAX('Planned Story Points'[Changed Date]),
                'Planned Story Points'[Changed Date] <= SprintEnd &&
                'Planned Story Points'[Iteration Path] = SprintPath
            ),
            "StoryPointsIfDone",
            CALCULATE(
                MAX('Planned Story Points'[Story Points]),
                FILTER(
                    'Planned Story Points',
                    'Planned Story Points'[Changed Date] = 
                        CALCULATE(
                            MAX('Planned Story Points'[Changed Date]),
                            'Planned Story Points'[Changed Date] <= SprintEnd &&
                            'Planned Story Points'[Iteration Path] = SprintPath &&
                            'Planned Story Points'[Work Item ID] = EARLIER('Planned Story Points'[Work Item ID])
                        ) &&
                    'Planned Story Points'[Iteration Path] = SprintPath &&
                    'Planned Story Points'[State] IN { "Done", "Closed" }
                )
            )
        ),
        NOT(ISBLANK([StoryPointsIfDone]))
    ),
    [StoryPointsIfDone]
)


    completed late story points:

    Completed Late Story Points = 
VAR SprintEnd = MIN('Planned Story Points'[Iteration End Date])
VAR SprintPath = SELECTEDVALUE('Planned Story Points'[Iteration Path])

RETURN
SUMX(
    FILTER(
        'Planned Story Points',
        'Planned Story Points'[Iteration Path] = SprintPath &&
        'Planned Story Points'[State] IN { "Done", "Closed" } &&
        'Planned Story Points'[Changed Date] > SprintEnd
    ),
    'Planned Story Points'[Story Points]
)


    Added during sprint:

    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
    )
)

    With my sample data, everything is working correctly. You might want to try it on your side, as this could help you resolve your issue.
    Regards,
    Harshitha.

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

      Anonymous thanks. I will try the dax and check and feedback 

      • SureshAnanth's avatar
        SureshAnanth
        Frequent Visitor
        1 year ago

        Anonymous I have tried to use Planned Story points DAX from the sample you have shared. However, I am still not able to match with ADO velocity chart for the squads for none of the iteration path. Hence I havent tried the other DAX, Completed is straight forward. I need to get the Planned accurately. Where are you using the values from Added During Sprint, since it is not shown in the chart visual. I had checked the data for one sprint for planned and it is not matching and same result for other sprints too