Use previous rows for calculation

[Anonymous]

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

[Ashish_Mathur]

Hi,

Are you looking for a Power Query solution?

[Anonymous]

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

[Ashish_Mathur · Accepted solution]

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]

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]

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

[Anonymous]

done, with pleasure