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

CategoryTypeDateDemandSalesWrong calcRight calcFormula
4090L16010-2-2024 00:00015600MIN(D2,E2)
4090L16031-5-2024 00:006555208208208MIN(D3-G2,E3)
4090L16025-11-2024 00:0015492609260926092MIN(D4-(G2+G3),E4)
4090L16030-12-2024 00:0015492888MIN(D5-(G2+G3+G4),E5)

 

Need Help

3 Replies

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.