Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fsim
Responsive Resident
Responsive Resident

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):

 

forcast_accuracy.png

 

 

 

 

 

 

 

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 productorderbook.png

 

 

 

 

 

 

 

 

 

 

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

 

 

 

 

 

 

 

 

 

 

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
 
 

 

 

 

1 ACCEPTED SOLUTION
fsim
Responsive Resident
Responsive Resident

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 !

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
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.
fsim
Responsive Resident
Responsive Resident

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 !

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.