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 data for other ratios.

Essentially, in the forecasted table I have:

Date              ClientID         ClientName             Future TCFOG               Future Total Commissions & Fees

3/31/2020     1                    ABC Company         4%                                $xxxxx

 

To calculate future total commissions & fees I need to multiply    (3/31/2019  total commissions & fees * Future TCFOG (specifically for 3/31/2020))

--the thing is 3/31/2019 total commissions & fees is located in my historical data table

Historical table:

Date               ClientID           ClientName                 Total Commissions & Fees

3/31/2019      1                      ABC Company             $1,000,000

 

If this is figured out, thats great! But then the next issue that stems is to calculate 2021 (and beyond) total commissions & fees, that has to be based on the 2020 data that we calculate above^. So all 2020 data is based on the historical table, but 2021-2025 data needs to be based on the forecasted table.

 

I will keep working on this thanks so much for your help already!

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

5 Replies

  • 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