Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Applying historical data to forecasts

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

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    HI Anonymous,

     

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI Anonymous,

     

    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