Show Last Known 6-Sprint Story Point Average for all Future Sprints?

Questions & Answers
1

Hi! First time poster, longtime lurker!

We as a company are trying to pursue better backlog health, and the way we’re doing that is by making sure at least two future sprints have enough story points in them to meet the average story point completion for the last 6 sprints. The formula for that would be “Story Points Created so far divided by the 6-Sprint Story Point average,” and if you get a 1 or above, your health is good for that future sprint.

I’m most of the way through automating this, but I can’t get EasyBI to carry forward a 5-sprint average for Sprints that aren’t closed yet (for obvious Reasons). That means I can show the backlog health for past Sprints, but I can’t quite automating this for future Sprints.

Does anyone have any ideas on how to carry forward the most recent closed sprint’s “Avg. Story Points Completed for the Last 6 Sprints” to a column for all future Sprints?

Thanks!

2

Hi @MagnumBI,
You contacted eazyBI support directly and we have solved the question there. But I just wanted to add the solution here in case there are users who are searching for a similar approach.
The formula is adjusted from the predefined “Sprint running velocity in board” from this report, condition for closed sprints is removed. The formula to get average for last 6 sprints.:

AVG(
    Tail(
      Filter(
        -- filter last 6 previous sprints in a board starting from current sprint
        [Sprint].CurrentHierarchyMember.FirstSibling:
        [Sprint].CurrentHierarchyMember,
        [Sprint].CurrentHierarchyMember.GetBoolean('Closed')
      ),
      -- set the count of last closed sprints for running velocity
      6 ) ,
    [Measures].[Sprint Story Points completed]
  )

best,
Gerda // support@eazyBI.com