Cumulative sum of calculated measure for tasks grouped by Epic

Questions & Answers
, ,
7

I have mostly a solution for this, I compute the sum for the cost of all tasks in each epic in a measure (gives the same value for each row/task that is in the same epic).

Then I use PreviousRowValue() to see when the epic has changed to a new one and substract the sum of all tasks in the previous row epic from the cumulative sum, this resets the cumulative sum to 0.
The adding of the current task cost and the subtraction of the previous epic cost has to happen inside the same CumulativeSum() expression in your measure (if you split this up into different CumulativeSum() calls then the different calls will not affect each other giving weird results), so it will look something like this:

Cast(CumulativeSum(
-- first the normal accumulation
    [Measures].[Task Cost]-
-- subtract the previous epic cost, in case this is the first row in a new epic
    IIf(PreviousRowValue([Measures].[Epic Name])<>[Measures].[Epic Name] -- PreviousRowValue may have a bug at the moment..discussing this with eazybi
    ,[Measures].[PreviousRowWholeEpicCost],0)
) as NUMERIC) --need to cast or IIf gets confused with types

To compute [Measures].[CurrentRowWholeEpicCost] you have to Sum the cost of all tasks in the given Epic. I would want to compute this by something like this:

-- pseudo code
Sum(
Filter(
VisibleRowSet(), --this has all the tasks and epic I care about, do not want to start with a slow [Issue].Members here
[Issue].CurrentMember.get('Epic Link') -- what is this addressing? the current item in VisibleRowSet iteration or the item in the current table row?
= 
[Issue.Epic].CurrentMember.key -- same here, this seems to address the current item in the visible row set whereas I would want to address the current epic in my table
),
[Measures].[Task Cost]
)

And [Measures].[PreviousRowWholeEpicCost] is simply

PreviousRowValue([Measures].[CurrentRowWholeEpicCost])

I got this to work by using ‘[Issue].Members’ (then the VisibleRowSet() and the issues one iterates over have a different hierarchy and can be addressed by that, so do not shadow each other) and applying some filters to the [Issue].Memebrs (only not closed epics) to get it reasonable fast…but it is still somewhat slow and it would be really good to understand how to filter parts of the VisibleRowSet() based on the current entry in the table (even if they have the same dimension).