Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Adding a Projection to Historical Data

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 ca...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

amitchandak's avatar
amitchandak
Super User
6 years ago

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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?

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Anonymous 

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