Forum Discussion

fsim's avatar
fsim
Responsive Resident
7 years ago
Solved

forcast accuracy with rolling version

Hi !

 

I'm stuck with this measure.

Every month we receive the forrecast of our sellers for the next 6 month.  On the other side I aslo have access to what was realIy sold per month over the last 12 month. I was asked to calculate the accuracy of each month based on the forecast that was done two month before. In other words: for the sales of jun, use the april forecast, for july, the may forecast and so on.

I can sumarize it like this (random numbers here):

 

 

 

 

 

 

 

 

On the data point of view, I have 2 main tables :

the first with the OrderBook that contains for every product/ customer /month the number of ordered product

 

 

 

 

 

 

 

 

 

 

the second with the forecast  for every product/ customer /month/ SOP version (forecast version)

 

 

 

 

 

 

 

 

 

 

my accuracy formula should be something like : (I split it in 3 fomulas )

1) Ord qty =  CALCULATE(sumx(OrderBook;OrderBook[Qty Ordered]))

2) Forcast M-2 qty = CALCULATE(SUMX('S&OP';[Forecasts Qty]);DATEADD('S&OP'[SOP version];-2; MONTH))

3) Forecast accuracy = IFERROR(CALCULATE(1-(ABS([Ord qty]-[Forcast M-2 qty])/[Forcast M-2 qty])); BLANK())

 
My guess  is that I need to link the dates column together in one way or another, but I don't find how :/
 
thanks in advance for your time
 
 

 

 

 

Need Help
  • fsim's avatar
    fsim
    7 years ago

    hi Anonymous  !

    Thank you for your suggestion.  In the interval I found a way to bypass the problem. In the Forcast table, I added a column  = if( DATEADD('S&OP'[Forecasts Month];-2;month)= 'S&OP'[SOP version];true;false)

    And I filter my calculation on this flag. 

    Problem solved.

    Thank you anyway !

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi fsim ,

    I think you need to add all/allselected function to break current row contents and use date function to define specific date for calculate.

    You can try to use following measures if they suitable for your requirement:

    Ord qty =
SUM ( OrderBook[Qty Ordered] )

Forcast M-2 qty =
VAR currdate =
    MAX ( 'S&OP'[SOP version] )
RETURN
    CALCULATE (
        SUM ( 'S&OP'[Forecasts Qty] );
        FILTER (
            ALLSELECTED ( 'S&OP' );
            [SOP version]
                = DATE ( YEAR ( currdate ); MONTH ( currdate ) - 2; DAY ( currdate ) )
        );
        VALUES ( 'S&OP'[Product] )
    )


Forecast accuracy =
1 - DIVIDE ( ABS ( [Ord qty] - [Forcast M-2 qty] ); [Forcast M-2 qty] )

    Time Intelligence "The Hard Way" (TITHW)

    Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT

    Regards,
    Xiaoxin Sheng

    • fsim's avatar
      fsim
      Responsive Resident
      7 years ago

      hi Anonymous  !

      Thank you for your suggestion.  In the interval I found a way to bypass the problem. In the Forcast table, I added a column  = if( DATEADD('S&OP'[Forecasts Month];-2;month)= 'S&OP'[SOP version];true;false)

      And I filter my calculation on this flag. 

      Problem solved.

      Thank you anyway !