topic Unable to refer previous row value of the current calculated column as we do in excel. Plz help in DAX Commands and Tips

Unable to refer previous row value of the current calculated column as we do in excel. Plz help

Rupak_bi — Tue, 19 Mar 2024 09:02:29 GMT

Unable to refer previous row value of the current calculated column as we do in excel. sample of the excel working attached. please guide

Re: Unable to refer previous row value of the current calculated column as we do in excel. Plz help

Anonymous — Wed, 20 Mar 2024 07:53:05 GMT

Hi Rupak_bi ,

This relates to the issue of circular dependencies encountered in creating calculated columns in powerbi. You may refer to the following blog:

Circular Dependency between Calculated Columns in ... - Microsoft Fabric Community

Realize circular dependency columns using M functi... - Microsoft Fabric Community

Best Regards,
Adamk Kong

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

Re: Unable to refer previous row value of the current calculated column as we do in excel. Plz help

Rupak_bi — Tue, 26 Mar 2024 12:08:16 GMT

hi Anonymous

Thanks for your reply. I have gone thorugh the articles but still unable to figureout the way to resolve it in dax. the source of the data is a calculated table and so cannot use query. please help if you have any idea to get those results.

Re: Unable to refer previous row value of the current calculated column as we do in excel. Plz help

Anonymous — Thu, 28 Mar 2024 05:29:56 GMT

Hi Rupak_bi ,

You can try formula like below:

first_ = IF ( 'Table'[Date] = MINX ( ALL ( 'Table' ), [Date] ), 0, BLANK () )

Column = VAR cur_date = 'Table'[Date] VAR min_date = MINX ( ALL ( 'Table' ), 'Table'[Date] ) VAR first_value = MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Date] = min_date ), [first_] ) VAR cur_stock = CALCULATE ( MAX ( 'Table'[Stock] ), FILTER ( ALL ( 'Table' ), 'Table'[Date] <= cur_date ) ) VAR next_ = CALCULATE ( SUM ( 'Table'[Maximum production] ), FILTER ( ALL ( 'Table' ), 'Table'[Date] <= cur_date ) ) + first_value VAR result_ = IF ( NOT ISBLANK ( 'Table'[first_] ), [first_], IF ( next_ < cur_stock, next_, cur_stock ) ) RETURN result_

Best Regards,
Adamk Kong

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