How do I sum the story points of the last 3 sprints?

Questions & Answers
1

I am trying to sum up the completed story points of the last three sprints starting with the current sprint. For example, if a team has 15 points for sprint 7, 12 for sprint 6, and 11 for sprint 5, I would like to know the total of that number. I am using the following calculated member -

SUM(
Tail(
– filter last 3 closed sprints starting from current sprint
Filter(
Head(
Cache(ChildrenSet([Sprint].[All closed sprints])),
Rank([Sprint].CurrentMember,
Cache(ChildrenSet([Sprint].[All closed sprints]))
)
),
– only sprints with committed story points are retrieved
[Measures].[Sprint Story Points committed] > 0
), 3
),
[Measures].[Sprint Story Points completed]
)

If I change it to look for just 1 sprint (changing 3 to 1) it comes back with the latest closed sprint points correctly. When I change it to 2 or 3 I get numbers that do not seem to correlate to any of the story points for any of the sprints. It could be I am not using the calculation properly. Perhaps someone could show me where I am wrong.

Thanks

2

Hi @rbryant11

You are on the correct path! However, I would suggest a bit simpler formula:

Sum(
  Tail(
    Filter(
      DescendantsSet([Sprint].CurrentMember,[Sprint].[Sprint]),
      [Measures].[Sprint closed?] MATCHES "Yes"
      AND
      [Measures].[Sprint Story Points committed] > 0
    ), 
    3
  ),
  [Measures].[Sprint Story Points completed]
)

The filter here will go through all of the Sprints and return a set of Closed Sprints that have Story Points committed. You don’t have to worry about the order here, the filter will retain the default order of the Sprints.
Next, the Tail function takes the last 3 Sprints (including the most recent one), and the Sum function will sum up all the complete Story Points from them.

Let me know if this fits your use case!

Best regards,
Nauris / eazyBI support

1 Like
3

Thank you for the response. This helped.