DAX Calculation based on Previous Row

[mo1988]

I have a dataset of columns (Index & Step 1)

I am trying to achieve the below calculations for Step 2 and Final

Any suggestions? Thanks

[mo1988]

@tamerj1 I attempted to replace the index row value from the matrix to Month-Year column (Sept 2022, Oct 2022, ...) - Now the numbers are not accurate, does this only work with index?

[tamerj1 · Accepted solution]

@mo1988 

It has to be of integer data or date (unique date) type. 

[mo1988]

@tamerj1 Thank you! This works like magic! thanks alot 🙂 

[tamerj1]

Hi @mo1988 
Please refer to attached sample file with the solution

Step 2 = 
VAR CurrentIndex = 'Table'[Index]
VAR TableBefore = FILTER ( 'Table', 'Table'[Index] < CurrentIndex )
VAR P1 = COUNTROWS ( TableBefore ) + 1
RETURN
    SUMX ( 
        TableBefore,
        VAR X = 'Table'[Step 1]
        VAR P = P1 - 'Table'[Index]
        RETURN
            ( 0.5 ) ^ P * x
    )

Step 3 = 'Table'[Step 1] + 'Table'[Step 2]