Forum Discussion
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...
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.
Hi,
Are you looking for a Power Query solution?
am looking to a way to calculate in DAX
currently, I set those codes:
Comm.Cons(net) =
CALCULATE(SUM('ShortageReport'[Ordered, Quantity]),
FILTER(ShortageReport, ShortageReport[RM / WIP Item Number] = StockReport[ITEM] && ShortageReport[Start Date] <= 'StockReport'[EXPIRY DATE]),
USERELATIONSHIP(ShortageReport[RM / WIP Item Number], 'StockReport'[ITEM]))
Released Q,H = CALCULATE(SUM('StockReport'[QTY ON HAND]), FILTER(StockReport, StockReport[ITEM] = Earlier(StockReport[ITEM]) && StockReport[Index] <= EARLIER(StockReport[Index])))
but both are not working as I cannot look into the value of previous row
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.
thanks. It does work.
I updated toStockReport[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 againYou are welcome. If my previous reply helped, please mark it as Answer.