Creating Forecasted Ratios using Historical & Forecasted Data

[ValueCreate]

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!

[Anonymous · Accepted solution]

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]

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

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]

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)?

[ValueCreate]

@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)?