Recursive Calculation of Table

[StepanResl]

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 with some pseudo code B(x-1)..., which is normaly working inside Excel.  Can someone help me please to provide this inside Power BI?

[v-lionel-msft]

Hi @StepanResl ,

I can't understand your pseudocode, can you give an example? For example,  how was the first line calculated?

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.

[StepanResl]

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]

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.

[StepanResl]

Hey,

thank you for your help, but unfortunately it isnt calculated right. In your Third row should be 1200 and then in fourth should be 1200+500 = 1700.

But thank you for your help. I am trying it same way, but i cannt figurite out, how to provide right results.

Kind Regards,

Steve

[Anonymous]

Hi @StepanResl.

Please do not try to squeeze a round peg into a square hole or... the other way round.

Use Power Query for this as this is the right tool, not DAX.

Best
D

[Greg_Deckler]

It's not really clear what you are doing here but if you are trying to do true recursion in DAX, you can't. https://www.linkedin.com/pulse/previous-value-recursion-dax-greg-deckler-microsoft-mvp-/