topic Re: Loop calculation in DAX Commands and Tips

Loop calculation

BCAdmin — Tue, 10 May 2022 20:53:15 GMT

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!

Re: Loop calculation

amitchandak — Wed, 11 May 2022 01:45:03 GMT

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])

Re: Loop calculation

Anonymous — Fri, 13 May 2022 05:48:35 GMT

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.