Forum Discussion
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 ...
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
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
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)?