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
Unable to refer previous row value of the current calculated column as we do in excel. sample of the excel working attached. please guide
Solved! Go to 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.
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.
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |