Forum Discussion

askpbiuser's avatar
askpbiuser
Icon for Helper I rankHelper I
2 years ago

DAX considering previous rows result in next rows but same column

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 lo...
Need Help
TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
2 years ago

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's avatar
askpbiuser
Icon for Helper I rankHelper I
2 years ago

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.