Forum Discussion

ttcalendar's avatar
ttcalendar
Frequent Visitor
2 years ago

Compare Forecast Version

Hello,

 

Every month we release a new revised forecast for our sales person to populate forward forecast numbers. I have another table which actual sales come through. When sales actualise, I need to calculate the variance between the previous month forecast and the actual sales but, then show for the following months, the revised forecast value to show the delta (difference row). 
I have slicer that shows the latest forecast version, eg, 202403 FC Version. 

 

Below are is an example snapshot what I'm looking for and the forecast table. 

 

 

Your help is much appreciated.

 

 

 

 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi ttcalendar ,

     

    Due to I don't know your data model, I create a sample to have a test.

    Forecast Table:

    Fact Table:

    Relationship:

    Measures:

    202402 FC Version = CALCULATE(SUM('Table'[Forecast]),FILTER('Table','Table'[FC Version] = "202402 FC Version"))
    202403 FC Version = CALCULATE(SUM('Table'[Forecast]),FILTER('Table','Table'[FC Version] = "202403 FC Version"))
    Acutual = CALCULATE(SUM(Fact[Sales]),FILTER(ALL('Fact'),Fact[Month] = MAX('Table'[Month])))
    Difference = 
IF([Acutual] = BLANK(),[202403 FC Version] - [202402 FC Version] ,[Acutual] - [202402 FC Version])

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

     

    • ttcalendar's avatar
      ttcalendar
      Frequent Visitor
      2 years ago

      Thanks for the help! Much appreciated.

       

      Instead of hard coding the forecast version in the calculate measures, I would like to present the user the option to select from 2 slicers to select the forecast versions they want to compare. 

       

      How can I incorporate this?

  • ttcalendar's avatar
    ttcalendar
    Frequent Visitor
    2 years ago

    Hi Anonymous,

     

    Thanks for the help! Much appreciated.

     

    Instead of hard coding the forecast version in the calculate measures, I would like to present the user the option to select from 2 slicers to select the forecast versions they want to compare. 

     

    How can I incorporate this?

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi ttcalendar ,

       

      I am confused about what your table looks like. Is Actual data and Forecast data in different tables like my sample or in the same table?

      Your calculation is based on your data model. Please share a sample file with me.

       

      Best Regards,
      Rico Zhou

       

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

      • ttcalendar's avatar
        ttcalendar
        Frequent Visitor
        2 years ago

        Hi Anonymous 

        Apologies, the actuals and forecasta data are in different tables like yours. Attached is a sample.  

         

        Sample PBI