Applying historical data to forecasts

[HFoy]

Hi All, 

I am wondering if it is possible to use historical figures to apply to future forecasts.

More specifically I have historic demand and historic delivery data by month and so know what % was delivered each month versus demand (e.g. December 18 demand = 4483, delivery =4989, %= 111.29). I also have demand forecasts for the next year and want to apply the historical % by month to these demand forecasts in order to calculate a delivery forecast (e.g December 19 demand forecast = 5109 (5109 *1.1129 = 5686) therefore delivery forecast = 5686)

Can anyone help?

Thanks

[v-shex-msft · Accepted solution]

HI @HFoy,

You can write variable to get current date and previous date, then use them to find out corresponded record and calculate the rate.

Forecast Delivery=
VAR currDate =
    MAX ( Historical[Date] )
RETURN
    CALCULATE (
        SUM ( Historical[Demand] ),
        FILTER (
            ALLSELECTED ( Historical ),
            YEAR ( [Date] ) = YEAR ( currDate )
                && MONTH ( [date] ) = MONTH ( currDate )
        )
    )
        * CALCULATE (
            SUM ( Historical[Delivery] ) / SUM ( Historical[Demand] ),
            FILTER (
                ALLSELECTED ( Historical ),
                YEAR ( [Date] )
                    = YEAR ( currDate ) - 1
                    && MONTH ( [date] ) = MONTH ( currDate )
            )
        )

If above not help, please share some sample data with same data structure for test.

Regards,

Xiaoxin Sheng

[Anonymous]

This is very broad, but I would build such a model with a calendar spanning all actuals plus the dates to be forecast. I would have  these measures:

LatestActualYear = maxx ( all ( historical[keyDate] ) , year ( historical[keyDate] ) )

ac_Demand = sum ( historical[demand] )

ac_Delivery = sum ( historical[delivery] )

ac_DeliveryRate = ac_Delivery / ac_Demand  /* ac_ is my prefix for measures that return actuals in the period in which they occurred */

fcb_DeliveryRate = calculate (ac_DeliveryRate, Filter(Calendar, calendar[year] = LastActualYear )) /* fcb_ stands for forecast basis and is usually nothing more than actuals in the period for which they will be used to forecast or a static period, as presented here. Another common form of the forecast basis is an average of multiple past periods. Whatever shape it takes, the key is you want your fcb measure to be aligned per the calendar to the forecast period in which it will be used. That's easier said that done, and highly dependent on your data, but for the scenario you described, the way I have filtered the year should work. */

fc_Delivery = fc_Demand * fcb_DeliveryRate