Use latest available price to calculate forecast

[LucySVK]

Hello,
I am trying to lookup the latest available price based on the Item code and use it to calculate cost in the future. Measure I have so far

  SUMX('Latest volume fcst Q', 'Latest volume fcst Q'[Forecasted volume] * 

CALCULATE(VALUES('FOB price Q'[SPP]), 

FILTER('FOB price Q', 

'FOB price Q'[Item code] = 'Latest volume fcst Q'[Item code] && 

LASTNONBLANK('FOB price Q'[Date],1))))

This, however only works when I display the dates for which I do have the price. What I'm trying to achieve is to display dates in the future for which I would calculate the price using the latest available price, e.g. price table has the price for 1/2/16, 1/3/16, 1/4/16. In my pivot I want to show 1/5/16, 1/6/16 and for these dates I'd like the cost to be calculated using the price from 1/4/16.

Thank you very much for your help,

Lucy

 

[FDibbins]

This will give you the latest date for a specified item...

	A​	B​	C​	D​	E​
2​	6/13/2016​	aa	1​	aa	6/19/2016​
3​	6/14/2016​	bb	2​
4​	6/15/2016​	cc	3​
5​	6/16/2016​	aa	4​
6​	6/17/2016​	bb	5​
7​	6/18/2016​	cc	6​
8​	6/19/2016​	aa	7​
9​	6/20/2016​	bb	8​
10​	6/21/2016​	cc	9​

E2=MAX(IF($B$2:$B$10=D2,$A$2:$A$10,0))
ARRAY entered, using CTRL SHIFT ENTER, not just enter

Once you have that - provided you wont have the same date for multiple items, you could use vlookup to return the value

 

[akice]

Would be very helpful to see sample file with pivot design or at least pic of model design, pivot and some sample data. Hard to help without specifics.

But i can say looking at your formula, the LASTNONBLANK won't work as used as it is a table function and is performing context transition inside the FILTER. This means that LASTNONBLANK function call as written will always return the currently iterated date in 'FOB price Q', not the last date in the table.

If you can provide more information this would help a lot with direction.

 

[LucySVK]

Hi akice
Fair point, sorry for not adding the data. Can't find an option to attach...?! So clumsily pasting into the body:

Item code Date FOB Supplier Currency SPP
32115 01/05/2016 22.3 Sup1 Dollar £15.22
32115 01/06/2016 22.3 Sup2 Dollar £15.22
32115 01/07/2016 22.3 Sup3 Dollar £15.22
32115 01/08/2016 22.3 Sup4 Dollar £15.22
32115 01/09/2016 20.3 Sup5 Dollar £13.85
32116 01/05/2016 26.18 Sup6 Dollar £17.86
32116 01/06/2016 26.18 Sup7 Dollar £17.86
32116 01/07/2016 26.18 Sup8 Dollar £17.86
32116 01/08/2016 26.18 Sup9 Dollar £17.86
32116 01/09/2016 26.18 Sup10 Dollar £17.86
60743 01/05/2016 19.15 Sup18 Dollar £13.07
60743 01/06/2016 19.15 Sup19 Dollar £13.07
60743 01/07/2016 19.15 Sup20 Dollar £13.07
60743 01/08/2016 19.15 Sup21 Dollar £13.07
60743 01/09/2016 19.15 Sup22 Dollar £13.07

Item code Forecasted volume Forecasted for date Cost of Kit
60743 5 01/06/2016 compare item code with item code in FOB price Q, find the latest date for that item code, take that SPP and multiply by Forecasted volume
60743 5 01/07/2016
60743 5 01/08/2016
60743 5 01/09/2016
60743 5 01/10/2016
60743 5 01/11/2016
60743 5 01/12/2016
60743 5 01/01/2017
60743 5 01/02/2017
60743 5 01/03/2017
60743 5 01/04/2017
60743 5 01/05/2017
60743 5 01/06/2017
60743 5 01/07/2017
60743 5 01/08/2017
60743 5 01/09/2017

Does that help at all?
I've also tried this code but Filter is giving me an error of too many arguments for FILTER:

= SUMx( 'Latest volume fcst Q', 'Latest volume fcst Q'[Forecasted volume]) *
CALCULATE(SUMMARIZE(TOPN(1,
FILTER(
ALL('FOB price Q'),
'FOB price Q'[Item code] = 'Latest volume fcst Q'[Item code],
[B]'FOB price Q'[Date]), [/B]'FOB price Q'[SPP])))

 

[anvg]

Hi
Sorry. I understood a bit from your description of a task. Maybe such solution can help you. Zippyshare.com - link by last date.xlsx
Regards,

 

[scottsen]

A few thoughts:
* I think things will go better if you have a real date/calendar table
* I think things will go better if you break the problem up into smaller measures
* Your gut to use LASTNONBLANK should work fine.
* Your call to FILTER() fails because you can't pass multiple clauses that way, ... use FilterA && FilterB, not FilterA,FilterB

 

[LucySVK]

For future folk - I overcomplicated it a little bit above. Here is the formula that works beautifully:

='Latest volume fcst Q'[Forecasted volume] * SUMMARIZE(TOPN(1, FILTER(ALL('FOB price Q'), 'FOB price Q'[Item code] = 'Latest volume fcst Q'[Item code]), 'FOB price Q'[Date]),'FOB price Q'[SPP])