Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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())
Solved! Go to 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 !
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
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 !
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |