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 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]

 

 

 

 

 

Need Help

11 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    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.

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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]

     

     

     

     

     

     

  • 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