Substract from Previous Calculated Member

Hey There,
I try substract a specific Number from the previous Result in the same Column.

For Example:
Week 1: Open Issue - 10 = 40
Week 2: 40 - 10 = 30
Week 3: 30 - 10 = 20

Week | Open Issue | Substract Value | Result

1 | 50 | 10 | 40
2 | 50 | 10 | 30
3 | 50 | 10 | 20

How can i get the Value of the Last result in the Same Column? I guess it is very simple but i tried for hours :slight_smile:

Hi @MircoGabriel

It is advisable to use cumulative measures instead or reference to the previous row to avoid looping within formulas and circular references.
You may then subtract this cumulative value from the base measure.

The easiest way to add a cumulative sum is by clicking on the column header and adding the cumulative sum.
Add cumulative sum

Once you have the cumulative value available - you may use that in the formula for the Result calculations.

[Measures].[Open issues]-
[Measures].[Cumulative subtract]

Please note that this cumulative measure name will not autofill - you have to enter the name precisely.
Depending on the report context, there might be other options for the cumulative sum calculations.
You may read about calculating the cumulative sum with time dimension here - How to get cumulative sum?

Kind regards,
Oskars /

Hi @oskars.laganovskis
Thanks for the answer. It works pretty well!

Now i have one step more to do.

The Substraction Value musst not be a constant value. it should be switch to 5 when the result is under a specific value. So the problem ist, that i have to use a CASE-WHEN-THEN Formular with pointing on values of itself. This causes to an error -.-

Greeting Mirco

Hi @MircoGabriel,

It seems you might have a rather complex setup.
Could you please describe the complete dependency of measures to be calculated and send to together with your current report definition?

Please specify what conditions define the Subtraction value and if it is dependant on the time dimension.

Once we receive full details, we will provide the best solution for your needs.

Kind regards,
Oskars //