Adding a Projection to Historical Data

[Anonymous]

Hello,

I am looking to take historical QOH data & be able to add on a calculated future projection to a model. I've added a picture of a small example I created.

I know the logic needed, but can't figure out how to do this with DAX & calculated measures. QOH, Demand, & POs Landing are all calculated measures, summing many rows of each respective category. I'd like the forecast to equal the QOH of the prior month plus the incoming POs plus the demand (negative).

My thought was below, but this just connected the historical data w/ the difference of Demand and POs Landing. How do I get the data to populate like what I circled in green?

QOH + Forecast = 
CALCULATE([Historical QOH (Measure)],LASTDATE([Date]) ) + [Forecast]

 

[amitchandak]

Your forecast seems to be pretty low. Do you want us to correct forecast calculation?

[Anonymous]

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

[amitchandak]

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

[Anonymous]

@amitchandak 

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?

[Ashish_Mathur]

Hi,

Here's a starting point.  See page 2 of this file.

[Anonymous]

@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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[amitchandak]

@Anonymous 

I think that is what it is on page 3. Please check.

[Anonymous]

@v-lionel-msft

@amitchandak 

I have included the formulas to my measures below, and here is a link to the .pbix file .

Historical QOH (Measure) = 
    SUM('zz - Practice QOH'[Historical QOH])

Demand (Measure) = 
    SUM('zz - Practice QOH'[Demand])

POs Landing (Measure) = 
    SUM('zz - Practice QOH'[POs Landing])

Forecast = 
    [Demand (Measure)] + [POs Landing (Measure)]

QOH + Forecast = 
    CALCULATE('zz - Practice QOH'[Historical QOH (Measure)],LASTDATE('zz - Practice QOH'[Date]) ) + [Forecast]

[v-lionel-msft]

Hi @Anonymous ,

If you want to calculate the values of [Historical QOH] during 2019/10-2019-12, you need to modify the formula of [Historical QOH]. So, we may need the formula of your three measures ([Historical QOH], [Demand], [POs Landing] ) and we also need a sample data.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Can you share sample data and sample output. If possible please share a sample pbix file after removing sensitive information.Thanks.
Proud to be a Datanaut My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...