Forum Discussion
Getting a rolling 3 months forecast accuracy
Thanks to you both Ashish_Mathur and v-jianboli-msft
I think what might have been lost in my example is the need that I sum up the absolute difference per Product ID. In my example there is only one product, so you approaches work. If I add a second Product ID B, with the following data:
| roduct ID | Date ID | Volume in LT | DataType |
| B | Jan 22 | 220 | Sales |
| B | Jan 22 | 200 | Forecast |
| B | Feb 22 | 200 | Sales |
| B | Feb 22 | 200 | Forecast |
| B | Mar 22 | 280 | Sales |
| B | Mar 22 | 200 | Forecast |
| B | Apr 22 | 200 | Sales |
| B | Apr 22 | 200 | Forecast |
| B | May 22 | 240 | Sales |
| B | May 22 | 200 | Forecast |
The expected result is this:
| Product ID | Date ID | Rolling 3M Sales | Rolling 3M FC | Abs Dif | FC Accuracy |
| A | Mar 22 | 315 | 330 | 15 | |
| B | Mar 22 | 700 | 600 | 100 | |
| Total | Mar 22 | 1015 | 930 | 115 | 88,7% |
| A | Apr 22 | 335 | 330 | 5 | |
| B | Apr 22 | 680 | 600 | 80 | |
| Total | Apr 22 | 1015 | 930 | 85 | 86,7% |
| A | May 22 | 330 | 330 | 0 | |
| B | May 22 | 720 | 600 | 120 | |
| Total | May 22 | 1050 | 930 | 120 | 88,6% |
As you can see, the total absolute difference for both products is not the result of ABS(([Sales A] + [Sales B]) - [Forecast A] + [Forecast B])), but instead the sum of the absolute differences of each product.
That is why I was using the SUMX-function. I am already using the combination of SUMX und SUMMARIZE-function for the calculation of the forecast accuracy for individual months. There it is working perfectly. Only if I want to now compare sales and forecast of 3 months, it is not working.
Maybe you have an idea how to solve this. I have added the second Product to your file Ashish_Mathur
You are welcome. Still very confused. How have you arrived at 135 - neither is that 80+5 nor is it 1015-930?
You are right! I changed the numbers while writing the post and forgot to update this one.