Forum Discussion

StepanResl's avatar
StepanResl
Frequent Visitor
6 years ago

Recursive Calculation of Table

Hey, I have table like this with columns Year, Month, Person, Static (Costs) and Revenue. I am trying to find out, how to provide last column inside Power BI. In column header I wrote Excel Formula ...
Need Help
StepanResl's avatar
StepanResl
Frequent Visitor
6 years ago

Hey v-lionel-msft ,

 

it works like:

(SUM value from same column (where were are calculating this), where we are still talking about same Person as in actual calculating row but with (Month - 1)) + (Value from column Revenue (in actual row) - Value from column Static (in actual row)) if result is lower then 0, then result should be zero.



 

v-lionel-msft's avatar
v-lionel-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi StepanResl ,

 

1. Add an [Index] column manually.

2. Create measures.

 

Measure 2 = 
VAR x = 
MAX(Sheet5[Revenue]) - MAX(Sheet5[Static]) 
RETURN
IF(
    x <0,
    0,
    x
)

Measure 3 = 
CALCULATE(
    [Measure 2],
    FILTER(
        ALLSELECTED(Sheet5),
        Sheet5[Index] = MAX(Sheet5[Index]) - 1 && Sheet5[Person] = MAX(Sheet5[Person])
    )
)

 

 

3. Create a measure again

 

Calculation = 
VAR x = MAX(Sheet5[Revenue]) - MAX(Sheet5[Static])
RETURN
x + [Measure 3]

 

Although my data is the same as yours, but I started taking values from October 2017, so the returned results are different.

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.