Today i tried to create some cumulated values within a pivot table with time on the X-axis. For that purpose I used two formulas, one for actual values and one for the cumuluated values:
Cumulate: =RangeSum(Actual,Before([Actual cum.]))
If there are values for each dimension the calculations work perfectly. However, if actual values are missing right from the beginning, then some random values will begin to pop up at a random months within the table. Those values are highlighted red in the graph below. Of course there should be zeros.
Strangely enough, those values will change if I change the label of the dimension "CostCentre"!!
Did anyone discover the same problem? What would be an alternative to calculate those values?
However, this formula doesn't work with multiple dimensions in a pivot. Attached you will find an example file, with multiple dimensions. The objective is to calculate a cumulative value with respect to Version, Leistungsart and Month. The cumulative and the actual value should both be sensitive to selections in Year and Period. Meaning, that only the values that i see should be cumulated.
I would really appreciate a solution to this problem!