Number of sprints worth of work is in the ready status and pointed

[Shamik89]

Hello,

I am trying to create a daily report that gives me the number of sprints worth of work we have that is pointed and in the ready status. Ideally, it would be Sum(Total committed points in ready status in a future sprint or backlog)/5 sprint running velocity.

Here is how far I got…

I created a calculated member in transition status to filter on ‘ready’ `

Aggregate({

Filter([Transition Status].Members,
[Transition Status].[Status].CurrentMember.Name matches ‘Ready’)
})

I also created a calculated member that only pulls in the stories in the backlog or future sprints

Aggregate(
Filter([Sprint].[Sprint].Members,
[Sprint].CurrentMember.Name = “(no sprint)” AND
NOT [Sprint].CurrentMember.GetBoolean(“Closed”) or
–sprint start date is in future or not set yet
(IsEmpty([Sprint].CurrentMember.get(‘Start date’)) OR
DateCompare([Sprint].CurrentMember.get(‘Start date’),
“today”) > 0)
)
)

I haven’t been able to get the running velocity to populate so I can divide it with the story points remaining number my report is deriving. Any help would be greatly appreciated!

[zane.baranovska]

Hi @Shamik89,

In the Demo account we have an example for calcaulted measures Story Points Velocity 5 Sprints to get the average speed based on the last closed sprints:

-- average velocity from last 5 closed sprints 
Avg(
  Tail(Order(
    -- filter last 5 closed sprints 
    Filter(
      Generate(
      { [Sprint].CurrentHierarchyMember,
         ChildrenSet([Sprint].CurrentHierarchyMember) },
      Descendants(
        [Sprint].CurrentHierarchyMember,[Sprint].[Sprint] )
      ),
        -- only sprints with committed story points are retrieved
        [Sprint].CurrentMember.getBoolean('Closed') AND
        NOT IsEmpty([Sprint].CurrentMember.get('Complete date')) AND
        ([Measures].[Sprint Story Points committed],[Time].CurrentHierarchy.DefaultMember) > 0
    ),
  -- order by completion date
  [Sprint].CurrentMember.get('Complete date'), BASC ) ,
  -- last 5 sprints
  5 ), 
  [Measures].[Sprint Story Points completed]
)

This calculation already covers Sprints within a code. You might want to remove Sprint and Transition Status dimensions from the report so selections would not contradict with calculated measures that already cover Sprints and Transition Statuses.

To sum up story points of futer sprints, you might want to use tuple of Story Point history, calculated member that groups Future Sprints (as you already have created) and specify the transition status “Ready” (that you also have created).

([Measures].[Story Points history],
[Transition Status].[Ready],
[Sprint].[Future sprints])

You may check Demo account for more examples on how to build Sprint and Agile analytics: Demo account

Best,
Zane / support@eazyBI.com

[Shamik89]

Thank you! Is there a way to show the velocity for everyday(even though it should be the same for everyday within a sprint)? WIth the code above i am able to get the velocity metric once every 2 weeks). Ideally, I would like to see a number of story points in ready divided by the velocity for ever day in a week(screenshot - this is the final desired output)

[Shamik89]

Not sure if it will help but here is what my current output is showing. Ideally, I would like to see 50 showing up from jan 15th to jan 27th and 72 from jan 28th to feb 12th.

[Shamik89]

Sorry for the multiple messages. I noticed when i use that the velocity calculation provided above i am only getting the velocity for 1 sprint. So for example on dec 4th the 22 point value is actually the number of story points the team completed for the sprint ending on that date. Not the average of the last 5 sprints. Do you know why this is happening?