Forum Discussion

tonyclifton's avatar
tonyclifton
Icon for Helper III rankHelper III
6 years ago
Solved

Calculation with value of previous row

Hello community,

 

I want to recreate the following simple Excel formula but can't get it right in Power Bi:

 

 

 

 

 

I want to create a calculated column (Deviation) based on the Value column, where each step takes the value of the previous step multiplied by 5%.

"Step" is only one column in Power Bi (one row value for each Step).

 

Hoping for some tips.


Thank you.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi, tonyclifton 

     

    You could add an index column after transposition, so you can create a calculate column using the DAX to reach your solution.

    Deviation =
VAR initialvalue = 50
RETURN
    IF ( Sheet1[Index] = 1, [Value], initialvalue * 0.95 ^ ( [Index] - 1 ) )


    Paul

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi, tonyclifton 

     

    You could add an index column after transposition, so you can create a calculate column using the DAX to reach your solution.

    Deviation =
VAR initialvalue = 50
RETURN
    IF ( Sheet1[Index] = 1, [Value], initialvalue * 0.95 ^ ( [Index] - 1 ) )


    Paul

    • tonyclifton's avatar
      tonyclifton
      Icon for Helper III rankHelper III
      6 years ago

      Thanks a lot Anonymous

      This is what I was looking for.