DAX Previous value from measure

[askpbiuser]

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.

[lbendlin]

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-...

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/1447...

[askpbiuser]

Thanks.
I have edited my post now to provide the Sample table and the Output I am looking for.

[v-jianpeng-msft · Accepted solution]

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.