Forum Discussion
Helper IDAX Previous value from measure
Hi All,
I have two fact tables connected with a dimension table. The fact tables have a many-to-one relationship with Dimension.
I have the below fields displayed in a table in Power BI visual.
I have category coming from Dim table
Date,Weeks, A (measure), Cumulative sum of A are fields and measures created using Fact Table 1.
Fact table 2 have values summed to make Measure B (Excel cell G above)
Cumulative sum of A - B gives difference of measure [Cumulative sum of A] - [B]. (Cell H)
All these measures are returning right values and fine so far.
nHowever,I am facing a challenge in calculating Calculation field (I) as its formula is MAX(Cumulative sum of A] - [B] - Previous result).
For example for row 2, result would be 0.
For row 3 result would be 0-0.
For row 4 result would be MAX(1147,83 -(0+0),0)= 1147,83
For row 5 result would be MAX(1066,57 -(1147,83+0+0),0)= 0
So its basically sum of previous rows result subtracted from current row. I tried creating in same way as I have done Cumulative sum of A measure, but since B is coming from Fact table 2 which does not have index, same concept doesn't apply there.
I am a bit lost and could you please help with the DAX measure.
Kindly let me know if something is unclear.
| Category | Date | Index | Weeks | A | Cumulative sum of A | B | Cumulative sum of A-B | Output |
| 402 | 12-10-2024 00:00 | 1 | 24 | 154,01 | 154,01 | 597,60 | 0 | 0,00 |
| 402 | 6-11-2024 00:00 | 2 | 27 | 501,22 | 655,23 | 669,58 | 0 | 0,00 |
| 402 | 21-11-2024 00:00 | 3 | 29 | 1.214 | 1.870 | 721,80 | 1147,83 | 1147,83 |
| 402 | 12-12-2024 00:00 | 4 | 32 | 0,55 | 1.870 | 803,20 | 1066,57 | 0 |
I have edited the post to include the sample data,
Output is the field which I am looking for.
Its formula as mentioned above is always MAX([Cumulative sum of A-B] - (previous row result).
So for example - considering row 3, MAX(1147,83 -(0+0),0)=1147,83, same is for all the rows as well. Row 1 just doesn't have any previous rows.
Thanks lbendlin
Hi, askpbiuser
Let's assume that your A, B, Cumulative sum of A, and Cumulative sum of A-B are all metrics. If not, you should adjust my DAX expression below appropriately.
I used the following DAX expression:
Output1 = VAR _currIndex = SELECTEDVALUE ( 'Table'[Index] ) VAR _previousvalue = CALCULATE ( [Measure of Cumulative sum of A-B], FILTER ( ALL ( 'Table' ), 'Table'[Index] < _currIndex ) ) VAR _result = [Measure of Cumulative sum of A-B] - _previousvalue RETURN SWITCH ( TRUE (), _result > 0, _result, 0 )Here are the results:
I've uploaded the PBIX file I used this time below.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- lbendlin
Super User
It is impossible to do this in DAX because DAX does not support conditional accumulation. The only way to do this is in Power Query via List.Accumulate.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
askpbiuserThanks.
I have edited my post now to provide the Sample table and the Output I am looking for.Thanks lbendlin
Hi, askpbiuser
Let's assume that your A, B, Cumulative sum of A, and Cumulative sum of A-B are all metrics. If not, you should adjust my DAX expression below appropriately.
I used the following DAX expression:
Output1 = VAR _currIndex = SELECTEDVALUE ( 'Table'[Index] ) VAR _previousvalue = CALCULATE ( [Measure of Cumulative sum of A-B], FILTER ( ALL ( 'Table' ), 'Table'[Index] < _currIndex ) ) VAR _result = [Measure of Cumulative sum of A-B] - _previousvalue RETURN SWITCH ( TRUE (), _result > 0, _result, 0 )Here are the results:
I've uploaded the PBIX file I used this time below.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
