Forum Discussion

Cali_2020's avatar
Cali_2020
Helper I
6 years ago
Solved

Current forecast vs previous forecast

Hello all,

 

My apologies in advance if this has already been answered in a previous post but I haven't found a solution to what I am trying to do.

 

On a monthly basis, my team performs a forecast on the current quarter and up coming fiscal quarters and compares the forecast to the previous month's forecast in order to describe the variance of what is expected to occur to close the current and future forecasted quarters. The current equation that has been working until recently is the following:

 

=CALCULATE( [Forecast Revenue],LASTNONBLANK(Data[Forecast Date],([Forecast Revenue])))

 

However, what is happening is that data is being fed into our database for future months' forecast so the results are from the future forecasts. These forecasts are in the future and I had been trying to apply a type of variable or filter to apply the results of the most recent [Forecast Date] only of that forecast date that is in the past with "today()". However, after many different attempts with multiple compbinations of variables, conditional if clauses, and time intelligence scenarios I either  receive an error or the results are blank.

 

Does anyone have any suggestions on how to fix this or where I can look to resolve this? (And to solve the previous forecast result? 🙂 )

 

Thank you!

 

 

Need Help
  • Cali_2020's avatar
    Cali_2020
    6 years ago

    Hi az38 ,

     

    This works great! Thank you! Just one more question, can I apply this measure to calculate the previous month's forecast? I tried the following but it returns the same results:

     

    =CALCULATE([Forecast - Revenues],DATEADD(Data[Forecast Date],-1,MONTH))

     

    Thanks again!

  • az38's avatar
    az38
    6 years ago

    Cali_2020 

    try

    var _maxPrevDate = CALCULATE(MAX(Data[Forecast Date]),Data[Forecast Date]<=EOMONTH(TODAY(),-1))

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

5 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Cali_2020 

    try smth like measure

    Measure = 
var _maxPrevDate = CALCULATE(MAX(Data[Forecast Date]),Data[Forecast Date]<TODAY())

RETURN
CALCULATE( [Forecast Revenue], Forecast Date=_maxPrevDate)

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

     

    • Cali_2020's avatar
      Cali_2020
      Helper I
      6 years ago

      Hi az38 ,

       

      This works great! Thank you! Just one more question, can I apply this measure to calculate the previous month's forecast? I tried the following but it returns the same results:

       

      =CALCULATE([Forecast - Revenues],DATEADD(Data[Forecast Date],-1,MONTH))

       

      Thanks again!

      • az38's avatar
        az38
        Community Champion
        6 years ago

        Cali_2020 

        try

        var _maxPrevDate = CALCULATE(MAX(Data[Forecast Date]),Data[Forecast Date]<=EOMONTH(TODAY(),-1))

         

        do not hesitate to give a kudo to useful posts and mark solutions as solution