Forum Discussion

ValueCreate's avatar
ValueCreate
Helper I
6 years ago
Solved

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...
Interesting Links
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
ValueCreate
Helper I
6 years ago

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