Forum Discussion

ValueCreate's avatar
ValueCreate
Helper I
6 years ago
Solved

Creating Forecasted Ratios using Historical & Forecasted Data

Hi, this is a follow up to my question yesterday   Now that I was able to calculate all the forecasted averages for the ratios, I am running into the issue of projecting the first years forecasted ...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi ValueCreate ，

    Please create one calculated column as below:

    Future Total Commissions & Fees = 
CALCULATE(MAX('Historical'[Total Commissions & Fees]),
    FILTER('Historical','Historical'[ClientID]='Forecasted'[ClientID]
        &&YEAR('Historical'[Date])+1=YEAR('Forecasted'[Date])
        &&MONTH('Historical'[Date])=MONTH('Forecasted'[Date])
        &&DAY('Historical'[Date])=DAY('Forecasted'[Date])))
*'Forecasted'[Future TCFOG]

    If you want to calculate the value for the future (after 2021), you can replace the historical table in the above formula with forecast table...

    Best Regards

    Rena

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi ValueCreate ，

Please create one calculated column as below:

Future Total Commissions & Fees = 
CALCULATE(MAX('Historical'[Total Commissions & Fees]),
    FILTER('Historical','Historical'[ClientID]='Forecasted'[ClientID]
        &&YEAR('Historical'[Date])+1=YEAR('Forecasted'[Date])
        &&MONTH('Historical'[Date])=MONTH('Forecasted'[Date])
        &&DAY('Historical'[Date])=DAY('Forecasted'[Date])))
*'Forecasted'[Future TCFOG]

If you want to calculate the value for the future (after 2021), you can replace the historical table in the above formula with forecast table...

Best Regards

Rena

  • ValueCreate's avatar
    ValueCreate
    Helper I
    6 years ago

    Anonymous Thanks! Am I able to keep all of the forecasted data in the same column that was calculated above (that is ideal) or would I have to create a new column for the forecasted data (2021-2025)?

  • ValueCreate's avatar
    ValueCreate
    Helper I
    6 years ago

    Anonymous I tried creating the 2021-2025 data in a column on its own based on your recommendation of replacing the historical data with the forecasted data but it is appearing blank. Ideally, if all of it could be in the same column on the same ratio that would be great. I am stuck as this might be a circular reference?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi ValueCreate ,

      If the field "Future TCF" originally exist in Forecast table, you can create a calculated column as below:

      Future Total Commissions & Fees2 = 
var FHistory= 
CALCULATE(MAX('Historical'[Total Commissions & Fees]),
    FILTER('Historical','Historical'[ClientID]='Forecast1'[ClientID]
        &&YEAR('Historical'[Date])=2019
        &&MONTH('Historical'[Date])=MONTH('Forecast1'[Date])
        &&DAY('Historical'[Date])=DAY('Forecast1'[Date])))*'Forecast1'[Future TCFOG] 
var FForecast=CALCULATE(MAX('Forecast1'[Future Total Commissions & Fees]),'Forecast1'[ClientID]=EARLIER('Forecast1'[ClientID]),
SAMEPERIODLASTYEAR(Forecast1[Date]))*'Forecast1'[Future TCFOG] 
return IF(YEAR('Forecast1'[Date])=2020,FHistory,IF(ISBLANK(FForecast),FHistory,FForecast))

      Best Regards

      Rena

      • ValueCreate's avatar
        ValueCreate
        Helper I
        6 years ago

        Future TCF did not orifinally exist, it was a calculated column (calculating 2020 data) that was created based on historical data. Is there anyway to a) calculate 2021 and beyond in the same column or b) create another calculated column (Future TCF1) thats based on that calculated column (Future TCF)?