Using Last Value as a calculation paramenter

[gitguto]

Hello everyone,

I have a table like this:

PRODUCT ID	MONTH	SALES QTY	SALES $$	PRICE	FCST QTY	FCST $$
A	FEB	10	200	20
A	MAR	10	220	22
A	APR	20	440	22
A	MAY	20	500	25
A	JUN	-	-		20	(20*25)
A	JUL	-	-		10	(10*25)
B	JAN	100	200	2
B	MAR	150	450	3
B	AUG	-	-		100	(100*3)

What I need to do is to calculate the last column (Forecast $$). In order to do so, I have to calculate the last practiced price ($$ Sold / Qty Sold in the last available month). And then, use it to multiply by the Sales Qty Forecast.

I tried =Sum(Aggr(FirstSortedValue([Sales $$]/[Sales Qty], -[DATE.autoCalendar.Month]), [PRODUCT ID])) but it returns me nothing.

Thank you!

[vinieme12 · Accepted solution]

As below

=Sum([FCST QTY])*
FirstSortedValue(total <PRODUCTID> aggr(sum([PRICE]),PRODUCTID,Month),-aggr(if(sum(PRICE)>0,Month),PRODUCTID,Month))

[gitguto]

Thank you! Works perfectly