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

[Rupak_bi]

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

[v-kongfanf-msft · Accepted solution]

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.

[Rupak_bi]

hi @v-kongfanf-msft 

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.

[v-kongfanf-msft]

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.