Forecasting Historical Data based on Client and Quarter

[ValueCreate]

Hi, this will probably be a long post so please bear with me. I have historical data on a list of clients, this data appears every quarter.

Here is how the table is set up for my historical data:        All historical quarters, followed by their Client ID # and the client name. client ID = client name every time, followed by columns of ratios

Effective Date     LegacyClientID      Client Name                Organic Growth Rate               Ratio Name              

12/31/2019         2                            ABC Company              5%

12/31/2019         4                            XYZ Company              4%

9/30/2019           2                            ABC Company             7%

9/30/2019           4                            XYZ Company             12%

6/30/2019           2

3/31/2019           2

...

3/31/2009           2                            ABC Company

3/31/2009           4                            XYZ Company

Now my main issue is trying to forecast this data going forward. Specifically, trying to take an average of a certain ratio for each client for each quarter. So essentially, I would want to look at ABC Company and take an average of their 3/31 quarters and project that going forward for their forecasted 3/31 quarters (along with doing the same for the other quarters).

Here is how the table is set up for my forecasted data:

ProjectedQuarterEndDate          LegacyClientID        Client Name                           Organic Growth Rate           

3/31/2020                                   2                              ABC Company

3/31/2020                                   4                              XYZCompany

6/30/2020                                   2                              ABC Company

6/30/2020                                   4                              XYZCompany

....

12/31/2025                                 2                               ABC Company

12/31/2025                                 4                               XYZCompany

Like I said before, for 3/31/2020, 3/31/2021, 3/31/2022 ... 3/31/2025   I would want the historical average Organic Growth rate for each client based on their historical 3/31 data (along with doing the same thing for 6/30, 9/30 & 12/31)

Any help would be greatly appreciated!!

[v-yiruan-msft · Accepted solution]

Hi @ValueCreate ,

Please create one calculated column as below in forecast table:

Forecast Organic Growth Rate =
AVERAGEX (
    FILTER (
        'History',
        'History'[LegacyClientID] = 'Forecasted'[LegacyClientID]
            && YEAR ( 'History'[Effective Date] )
                < YEAR ( 'Forecasted'[ProjectedQuarterEndDate] )
            && MONTH ( 'History'[Effective Date] )
                = MONTH ( 'Forecasted'[ProjectedQuarterEndDate] )
            && DAY ( 'History'[Effective Date] )
                = DAY ( 'Forecasted'[ProjectedQuarterEndDate] )
    ),
    'History'[Organic Growth Rate]
)

If the above methods are not applicable in your scenario, please correct me and provide more explanation.

Best Regards

Rena

[ValueCreate]

Thank you that worked great!! I will continue to work on this so I may have more questions