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
v-lionel-msft's avatar
v-lionel-msft
Community Support
6 years ago

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'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
    Community 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.

     

    • StepanResl's avatar
      StepanResl
      Frequent Visitor
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago
        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