Trying to calculate remaining number of sprints to complete an Epic

Questions & Answers
1

I have created a report using our Jira hierarchy Initiative and Epic (Initiaitve being our highest level).
The report sums the number of issues created, resolved , story points created, story points closed at the Epic level. I included a measure Story Points Velocity 6 Sprints on the report then calculated the number of Sprints remaining using (Story Points Created - Story Points Closed / Story Points Velocity 6 Sprints). I get a result but my question is it valid to use the Story Points Velocity 6 Sprints at the Epic level. The following is the code in the measure Story Points Velocity 6 Sprints to determine # of sprints remaining to complete the work?

-- average velocity from last 6 closed sprints 
Avg(
  Tail(Order(
    -- filter last 6 closed sprints of any sprint selection or from all sprints
    Filter(
      DescendantsSet([Sprint].CurrentHierarchyMember,[Sprint].CurrentHierarchy.Levels("Sprint")),
        -- only sprints with committed story points are retrieved
        [Sprint].CurrentHierarchyMember.getBoolean('Closed') AND
        NOT IsEmpty([Sprint].CurrentHierarchyMember.get('Complete date')) AND
        ([Measures].[Sprint Story Points completed],[Time].CurrentHierarchy.DefaultMember) > 0
    ),
  -- order by completion date
  [Sprint].CurrentHierarchyMember.get('Complete date'), BASC ) ,
  -- last 6 sprints
  6 ), 
  [Measures].[Sprint Story Points completed]
)

Thanks as always for your help.

2

Hey @NickSocci,

Yes, you can use this calculation at the Epic level as well.
The calculation takes into account only the 6 sprints related to the selected Epic. The filter criteria search for sprints that have any completed story points for the selected epic:

([Measures].[Sprint Story Points completed],
[Time].CurrentHierarchy.DefaultMember) > 0

Please refer to the “Story points progress and velocity by sprints in epic” report in the Demo account: Story points progress and velocity by sprints in epic - Issues - Jira Demo - eazyBI.

If you’re unsure about how the calculation works, you can disassemble and check each part separately. For example, you can see which sprints are returned by the Filter function for each epic and how the set changes when it gets ordered, and only the last 6 are returned. The [SetToStr()] function is very handy.
Here’s a training video “MDX debugging: How to see through calculated measure” for more details: Training videos on specific topics.

Best,
Zane / support@eazyBI.com