forcast accuracy with rolling version

[fsim]

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

[v-shex-msft]

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 · Accepted solution]

hi @v-shex-msft  !

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 !