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
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
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
Code:
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))))
Thank you very much for your help,
Lucy