Forum Discussion
Adding a Projection to Historical Data
What I'd like is for the Forecast be the last month with actual QOH PLUS the forecast for each month (based on demand and incoming POs). I could just enter the previous month's amount and sum that with the demand and POs, but I'd like to use time intelligence so I don't have to go into the file to change it. This way it would look like the Historical QOH w/ the red line in my first post.
Here is my attempt that doesn't do what I want it to do.
QOH + Forecast =
CALCULATE('zz - Practice QOH'[Historical QOH (Measure)],LASTDATE('zz - Practice QOH'[Date]) ) + [Forecast]Here is the code for the below chart. What I'd like is to have a measure that produces the line from Historical QOH for all past dates, and Last Month's QOH + Forecast for the future dates.
QOH + Forecast =
[Forecast] + 1550
I got a measure like this. You need to adjust the other than _last, That I made constant now.
Final POH =
var _max = maxx(filter(all('zz - Practice QOH'),'zz - Practice QOH'[Historical QOH]>0),'zz - Practice QOH'[Date])
Var _last =sumx(SUMMARIZE('zz - Practice QOH',"_max1",maxx(filter(all('zz - Practice QOH'),'zz - Practice QOH'[Date]>=_max),'zz - Practice QOH'[Historical QOH])),[_max1])
return _last+SUM('zz - Practice QOH'[Demand])+SUM('zz - Practice QOH'[POs Landing])
Please find the attached file
We are closer! Thanks!
Now, can we get the "Final POH" to be the Historical QOH for all past dates, and your projections for future dates?
Anonymous
I think that is what it is on page 3. Please check.
Hi,
Here's a starting point. See page 2 of this file.
Ashish_Mathur amitchandak v-lionel-msft
Still not there. I need to be able to produce a table that looks like this, where QOH matches historically & projection is the last known historical inventory plus POs & Demand.Date Historical QOH (Measure) Demand (Measure) POs Landing (Measure) Final QOH (intermediary) 1/1/2019 1300 1300 2/1/2019 1200 1200 3/1/2019 1400 1400 4/1/2019 1450 1450 5/1/2019 1350 1350 6/1/2019 1000 1000 7/1/2019 1400 1400 8/1/2019 1700 1700 9/1/2019 1550 1550 10/1/2019 -250 210 1510 11/1/2019 -200 225 1575 12/1/2019 -245 285 1590
