Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ValueCreate
Helper I
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
v-yiruan-msft
Community Support
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]
)

forecast.JPG

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.

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
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]
)

forecast.JPG

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.

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.