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
- "(net) Comm Cons": this column should contain the difference when column "Comm.Cons" changes value
- "Released, Q, H": contains the cummulate sum of "Qty on hand" but, when "Comm.Cons" changes value, the cummulative sum should restart from the current row.
any help will be much appreciated
| Qty on hand | Date | Comm. Cons | (net) Comm Cons | Released, Q, H | Qty Expired less consumption |
| corrected | |||||
| 1 | 10/32/22 | 1 | 1 | ||
| 1 | 10/32/22 | 2 | 2 | ||
| 30 | 7/31/2023 | 7 | 7 | 32 | 25 |
| 6 | 7/31/2023 | 7 | 7 | 38 | 31 |
| 20 | 7/31/2023 | 7 | 7 | 58 | 51 |
| 20 | 7/31/2023 | 7 | 7 | 78 | 71 |
| 20 | 7/31/2023 | 7 | 7 | 98 | 91 |
| 13 | 7/31/2023 | 7 | 7 | 111 | 104 |
| 2 | 4/30/2025 | 81 | 74 | 2 | 104 |
| 48 | 4/30/2025 | 81 | 74 | 50 | 104 |
| 5 | 4/30/2025 | 81 | 74 | 55 | 104 |
| 48 | 4/30/2025 | 81 | 74 | 103 | 133 |
| 10 | 9/30/2025 | 100 | 19 | 10 | 133 |
| 10 | 9/30/2025 | 100 | 19 | 20 | 134 |
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.
6 Replies
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 rowHi,
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.