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)
)
