cancel
Showing results for
Did you mean:

## 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 😕

1 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 !

2 REPLIES 2
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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 !

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.