DAX considering previous rows result in next rows but same column

[askpbiuser]

Hi All,

I have the below DAX summarized Table created using measures, which are returning right values till Sales column. I have to create another calculation say Right calc whose formula is to look at Min of Demand and Sales columns but considering the previous rows cumulative value of Right calc in the next subsequent rows. So the complexity is the cumulative sum of previous rows in the same calculation

I assumed its Min(Demand, Sales) in my Wrong calc calculation. However the calculation expected by business is what is mentioned in the Formula below. The Right Calc rightly displays the expected ouput as an example.I do have many categories, but for similicity I have taken one example.

Could someone please help on creating the Right calc column/measure or guide me on how to do it ?

Category	Type	Date	Demand	Sales	Wrong calc	Right calc	Formula
4090	L160	10-2-2024 00:00	0	156	0	0	MIN(D2,E2)
4090	L160	31-5-2024 00:00	6555	208	208	208	MIN(D3-G2,E3)
4090	L160	25-11-2024 00:00	15492	6092	6092	6092	MIN(D4-(G2+G3),E4)
4090	L160	30-12-2024 00:00	15492	8	8	8	MIN(D5-(G2+G3+G4),E5)

[v-tangjie-msft]

Hi @askpbiuser ,

Thanks @TomMartens  for the quick reply.

DAX can not realize such recursive calculation, you can use EXCEL to create formulas to get the results and then import them into PBI Desktop as a data source.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[TomMartens]

Hey @askpbiuser ,

I can not see a difference between Wrong and calcRight.

Nevertheless, you have to be aware of the fact that there is no implicit order of rows inside a table. This means that you have to use DAX windowing functions in combination with the partitionBy switch. Here you will find an introduction to WINDOW: WINDOW function (DAX) - DAX | Microsoft Learn

Hopefully, this helps to tackle your challenge.

Regards,

Tom

[askpbiuser]

Thanks Tom.
The difference between Wrong cal and Right calc formula is -
Wrong calc - MIN(Demand,Sales)
Right calc - MIN(Demand,Sales) for first row and then
MIN(Demand - (Sum of previous (Right calc result),Sales)
To note here is the MIN formula which keeps changing and takes cumulative total into consideration.
Please let me know if more information needed.
I still had no luck in getting the right ans.