Sum up the Hours Spent in the Last Closed Sprint and another for the One Prior Closed Sprint

I am trying to sum the hours spent in the last closed sprint and another measure for the one prior closed sprint.

Below is the “Previous Sprint” measure

Sum(
Filter(
DescendantsSet([Sprint].CurrentHierarchyMember, [Sprint].[Sprint]),
[Measures].[Last closed sprints by boards] = 1
),
[Measures].[Hours spent]
)

And another measure called “Previous Sprint -1” :

Sum(
Filter(
DescendantsSet([Sprint].CurrentHierarchyMember, [Sprint].[Sprint]),
[Measures].[Last closed sprints by boards] = 2
),
[Measures].[Hours spent]
)

The hours spent within the sprint is not summing up as it should. Can you please advise on the MDX code?

Much appreciated.

Hi @Keiko,

Your cautions are valid options for focusing on the last closed Sprint and the one before the last closed Sprint. Also, “Hours spent” is the right measure for this calculation.
The only drawback might be that if you have several Sprint Boards imported into the eazyBI, then calculations sum up all the last sprints for each Board.

One solution is to use the Sprint dimension on report rows and select the Board level.

Another option is to use calcualted member “Last 5 closed sprints from all boards” as a base for your calculations to get “Hours spent” only from the one Sprint across all Boards.
Use function item() to address a specific Sprint member in the set; note that item(0) returns the first member from the set. And then you can use this member in a tuple construction with measure “Hours spent”.

The calcauted measure to get the Hours spent in the last closed Sprint might look like this:

(
  [Measures].[Hours spent],
  --get the last closed sprint
  Generate(
  -- get all boards
    [Sprint].[Board].Members,
    -- access all children - sprints from the board and get the last 5 closed
    Tail(
      Filter(
      [Sprint].CurrentMember.Children,
      [Sprint].CurrentMember.GetBoolean("Closed")),
      5)
  ).item(0)
)

And the calcauted measure to get the Hours spent in one before last closed Sprint might look like this:

(
  [Measures].[Hours spent],
  --get the last closed sprint
  Generate(
  -- get all boards
    [Sprint].[Board].Members,
    -- access all children - sprints from the board and get the last 5 closed
    Tail(
      Filter(
      [Sprint].CurrentMember.Children,
      [Sprint].CurrentMember.GetBoolean("Closed")),
      5)
  ).item(1)
)

Best,
Zane / support@eazyBI.com