cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

## Forecasting Historical Data based on Client and Quarter

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!!

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors