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 % ...
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

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