Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Rupak_bi
Responsive Resident
Responsive Resident

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

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

 

Rupak_bi_0-1710838913566.png

 

1 ACCEPTED SOLUTION
v-kongfanf-msft
Community Support
Community Support

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_

vkongfanfmsft_0-1711603748255.png

 

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.

View solution in original post

3 REPLIES 3
v-kongfanf-msft
Community Support
Community Support

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_

vkongfanfmsft_0-1711603748255.png

 

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
Responsive Resident
Responsive Resident

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
Community Support
Community Support

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.