Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Use previous rows for calculation

dear all,   I'm in need of a suggestion for how to calculate a column on base of the comparison of previous rows of other columns.   My data set is as follows, what I need to calculate is  - "(n...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    This calculated column formula works provided the entries in the "Comm. Cons" column are in ascending order.

    (net) Comm. Cons = [Comm. Cons]-CALCULATE(MAX(Data[Comm. Cons]),FILTER(Data,Data[Date]<EARLIER(Data[Date])))

    Hope this helps.

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

Hi,

This calculated column formula works provided the entries in the "Comm. Cons" column are in ascending order.

(net) Comm. Cons = [Comm. Cons]-CALCULATE(MAX(Data[Comm. Cons]),FILTER(Data,Data[Date]<EARLIER(Data[Date])))

Hope this helps.

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

thanks. It does work.

I updated to 

StockReport[Cummulative Consumption] - CALCULATE(MAX(StockReport[Cummulative Consumption]), FILTER(StockReport, StockReport[ITEM]=earlier(StockReport[ITEM]) && StockReport[EXPIRY DATE]<EARLIER(StockReport[EXPIRY DATE])))

would you have a solution also for the second column, "Released Q,H" so to restart the cumulative sum when "Comm.Cons" changes?

thanks again
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    You are welcome.  If my previous reply helped, please mark it as Answer.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      done, with pleasure