I found a partial answer to my question here (how to exclude certain levels from the cumulative sum): Cumulative sum of calculated measure for tasks grouped by Epic - #5 by ilze.mezite
But it only partially solves my problem.
I want to do a cumulative sum per fix version in one table (and exclude the tasks):
So in the table shown I want to sum only items that have the same “Issue Fix Version Selected”. So somehow I need to reset the sum when a new “Issue Fix Version Selected” is encountered (which I tried to do as explained in my original post).
My original solution tries to replace the cumulative sum by the current remining estimated hours when a nee fix version is encountered (but this does work only sometimes as expected):
IIf([Measures].[Issue type]='Task',0,
-- same fix version: cumulate
IIf(Cast(PreviousRowValue([Measures].[Issue Fix Versions Selected]) as String)
=Cast([Measures].[Issue Fix Versions Selected] as String)
,
--different version.."reset" cumulated value
CumulativeSum([Measures].[Remaining estimated hours])
,
[Measures].[Remaining estimated hours]
)
)
If I could compute the cumulativesum myslef in a measure this would be easy, unfortunately this is forbidden:
[Measures].[Remaining estimated hours]+PreviousRowValue([Measures].[name of current measure])
Here is an example what I want to get out:
Epic | Remaining Hours | Fix Version | CumSumPer Fix Version
EP-1| 10 | FV1| 10
EP-2| 11 | FV1| 21 --10+11
EP-3| 12 | FV2| 12 --new fixversion, reset cum sum to remaining hours of first epic in fixvesrion
EP-4| 13 | FV2| 25 – 12+13
EP-5| 14 | FV3| 14 --new fixversion, reset cum sum