Loop calculation

[BCAdmin]

Hi All,

I'm trying to do this on Power BI, but I can't figure out the formula to do this. I want to use the previous calculated value from Column E to the next row and so on. Is it possible to be done on Power BI?

Below is the formula on Excel:

The data value:

Thank you!

[Anonymous · Accepted solution]

Hi @BCAdmin ,

I have a test on amitchandak's code and I find that I won't return the result you want. Firstly, we need to add an Index column in Power Query if you only have month column in text format.

Your table will be like as below.

Then add calculated columns in this table.

Column4 = 
VAR _START =
    CALCULATE ( SUM ( 'Table'[Column1] ), ALL ( 'Table' ) )
RETURN
    _START
        + SUMX (
            FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ),
            [Column2] - [Column3]
        )

New Column1 = 
VAR _PREVIOUS_COLUMN4 =
    CALCULATE (
        SUM ( 'Table'[Column4] ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
    )
RETURN
    IF ( ISBLANK ( 'Table'[Column1] ), _PREVIOUS_COLUMN4, 'Table'[Column1] )

Result is as below.

Best Regards,
Rico Zhou

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

[amitchandak]

@BCAdmin , Assume you have date or index column (add in power query)

You can create a new column

[Column1] - Sumx(filter(Table, [Date] <= earlier([date]) ), [Column2] -[Column3])