Calculating sum of points, changes to points over past three sprints

Questions & Answers
1

I am trying to create a report that only looks back at the past three sprints by team. The report will show the following in a table, but I can not figure where to start. I need to get a table with the following three items in a table. Once I have these calculated members I will be able to do the rest. I am just really struggling with the calculated members.

Sum of Points Committed in past 3 sprints
Sum of Points added after sprint start for past 3 sprints
Sum of Points removed after sprint start for past 3 sprints

Let me know if there are other items that would be useful for me to provide on this, and thank you for any support you can provide.

2

Hi @vcuncg

Welcome back to community!

You may want to create a calculated member like “Last 3 closed sprints” in the Sprint dimension:

Aggregate(
  Tail(Order(
Filter(
  [Sprint].[Sprint].Members,
  [Sprint].CurrentMember.getBoolean('Closed')
  AND
  NOT isEmpty([Sprint].CurrentMember.get('Complete date'))
), 
-- ordering by completion date across all boards
[Sprint].CurrentMember.get('Complete date'), BASC
  ), 3)
)

This would return last 3 sprints from all imported sprints.
If you would want to get last 3 closed sprints from a particular board (the closest I can imagine with “team sprints”), then the formula would be slightly different (use correct board name instead of [Sprint].[BOARD_NAME].Children):

Aggregate(
  Tail(Order(
Filter(
  [Sprint].[BOARD_NAME].Children,
  [Sprint].CurrentMember.getBoolean('Closed')
  AND
  NOT isEmpty([Sprint].CurrentMember.get('Complete date'))
), 
-- ordering by start date across all boards
[Sprint].CurrentMember.get('Complete date'), ASC
  ), 3)
)

Then use this member in the report rows or as a Page filter.

Add measures “Sprint Story points added”, “'Sprint Story points removed” and create a new measure “Sprint Story points committed (for multiple sprints)” and select also in the report table.
“Sprint Story points committed (for multiple sprints)” would be with the following formula:

( [Measures].[Story Points added],
  [Transition Field].[Sprint status],
  [Sprint Status].[Active],
  -- An issue was in a sprint at a sprint start time
  [Issue Sprint Status Change].[Future => Active]
)

Could it be a solution for you?

Best,
Ilze, support@eazybi.com

3

Thank you for the response. I will go in and test this now and let you know.