Forecasting Historical Data based on Client and Quarter

Question

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.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Here is how the table is set up for my historical data:&nbsp; &nbsp; &nbsp; &nbsp; All historical quarters, followed by their Client ID # and the client name. client ID = client name every time, followed by columns of ratios&nbsp;Effective Date&nbsp; &nbsp; &nbsp;LegacyClientID&nbsp; &nbsp; &nbsp; Client Name&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Organic Growth Rate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Ratio Name&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;12/31/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ABC Company&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5%12/31/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; XYZ Company&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4%9/30/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ABC Company&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7%9/30/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; XYZ Company&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12%6/30/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;23/31/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2...3/31/2009&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ABC Company3/31/2009&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; XYZ Company&nbsp;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).&nbsp;Here is how the table is set up for my forecasted data:&nbsp;ProjectedQuarterEndDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LegacyClientID&nbsp; &nbsp; &nbsp; &nbsp; Client Name&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Organic Growth Rate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3/31/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;ABC Company3/31/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;XYZCompany6/30/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;ABC Company6/30/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;XYZCompany....12/31/2025&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ABC Company12/31/2025&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;XYZCompany&nbsp;Like I said before, for 3/31/2020, 3/31/2021, 3/31/2022 ... 3/31/2025&nbsp; &nbsp;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 &amp; 12/31)&nbsp;Any help would be greatly appreciated!!&nbsp;

anonymous · Accepted Answer

Hi&nbsp;ValueCreate&nbsp;,Please create one calculated column as below in forecast table:Forecast Organic Growth Rate =
AVERAGEX (
    FILTER (
        'History',
        'History'[LegacyClientID] = 'Forecasted'[LegacyClientID]
            &amp;&amp; YEAR ( 'History'[Effective Date] )
                &lt; YEAR ( 'Forecasted'[ProjectedQuarterEndDate] )
            &amp;&amp; MONTH ( 'History'[Effective Date] )
                = MONTH ( 'Forecasted'[ProjectedQuarterEndDate] )
            &amp;&amp; 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 RegardsRena

Forum Discussion

Forecasting Historical Data based on Client and Quarter

2 Replies

Related content

Visualizing Historical Data

Forecasting with Parameters from Historical Data

Help Needed: Historical Forecast Line Chart in Power BI

Forecasting with DAX

Historical Log support