Forum Discussion

ValueCreate's avatar
ValueCreate
Icon for Helper I rankHelper 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
Icon for Helper I rankHelper 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)?