Getting a rolling 3 months forecast accuracy

[Anonymous]

I am working currently on a moving 3 months forecast accuracy. My fact table is structured like the table below with all the facts in one column (Volume in LT) and a datatype column indicating what fact it is. There is also a date dimension table (Date_DIM), which has a 1:m relationship with the Date ID column of the fact table. To show you, what I mean, I added one table below with the facts and below that another table with the desired results:

Product ID	Date ID	Volume in LT	DataType
A	Jan 22	100	Sales
A	Jan 22	110	Forecast
A	Feb 22	105	Sales
A	Feb 22	110	Forecast
A	Mar 22	110	Sales
A	Mar 22	110	Forecast
A	Apr 22	120	Sales
A	Apr 22	110	Forecast
A	May 22	100	Sales
A	May 22	110	Forecast

Date ID	3M Sales	3M Forecast	Abs Diff 3M	FCA
Mar 22	315	330	15	95,2%
Apr 22	335	330	5	98,5%
May 22	330	330	0	100%

 I am using the following function to calculate the 3M cumulated Sales and Forecast, which give me the desired results:

Sales.Rolling3M = 

CALCULATE(SUM(FactTable[Volume in LT]), FactTable[DataType] = "Sales", DATESINPERIOD(Date_DIM[Date ID], ENDOFMONTH(Date_DIM[Date ID]),-3,MONTH))

Forecast.Rolling3M =

CALCULATE(SUM(FactTable[Volume in LT]), FactTable[DataType] = "Forecast", DATESINPERIOD(Date_DIM[Date ID], ENDOFMONTH(Date_DIM[Date ID]),-3,MONTH))

For the absolute difference I use this formula:

AbsDif = SUMX(

    SUMMARIZE(FactTable, Date_DIM[Date ID] ,FactTable[Product ID], "Sales LT", [Sales.Rolling3M], "Forecast LT", [Forecast.Rolling3M]),

    ABS([Forecast LT] - [Actuals LT]))

For some reason the result of this formula is not the absolute difference of 3M Sales and 3M Forecast. Instead it is only the absolute difference of that particular month. The table looks like this:

Date ID	3M Sales	3M Forecast	Abs Diff 3M	FCA
Jan 22	100	110	10	90%
Fen 22	205	220	5	95,2%
Mar 22	315	330	0	100%
Apr 22	335	330	10	91,7%
May 22	330	330	10	90%

How can I change the formula of the AbsDif meassure to get the absolute difference between the 3M Sales and 3M Forecast? 

Your help is much appreciated! 

[v-jianboli-msft]

Hi @Anonymous ,

Please try:

AbsDif =
VAR _a =
    SUMMARIZE (
        FactTable,
        Date_DIM[Date ID],
        FactTable[Product ID],
        "Sales LT",
            CALCULATE (
                SUM ( FactTable[Volume in LT] ),
                FactTable[DataType] = "Sales",
                FILTER (
                    ALL ( FactTable ),
                    [Date ID] <= EOMONTH ( EARLIER ( Date_DIM[Date ID] ), 0 )
                        && [Date ID] > EOMONTH ( EARLIER ( Date_DIM[Date ID] ), -3 )
                        && [Product ID] = EARLIER ( FactTable[Product ID] )
                )
            ),
        "Forecast LT",
            CALCULATE (
                SUM ( FactTable[Volume in LT] ),
                FactTable[DataType] = "Forecast",
                FILTER (
                    ALL ( FactTable ),
                    [Date ID] <= EOMONTH ( EARLIER ( Date_DIM[Date ID] ), 0 )
                        && [Date ID] > EOMONTH ( EARLIER ( Date_DIM[Date ID] ), -3 )
                        && [Product ID] = EARLIER ( FactTable[Product ID] )
                )
            )
    )
VAR _b =
    SUMX ( _a, [Sales LT] )
VAR _c =
    SUMX ( _a, [Forecast LT] )
RETURN
    SUMX ( _a, ABS ( [Forecast LT] - [Sales LT] ) )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

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 

Getting a rolling 3 months forecast accuracy.pbix

[Ashish_Mathur]

You may download my PBI file from here.

Hope this helps.

[medeirosh]

Hi @Ashish_Mathur and @Anonymous , do you still have this file? I tried to download but it was not found? 
Thank you in advance.
Henrique M. 

[Ashish_Mathur]

No, i do not have the file.  Share some data, explain the question and show the expected result.

[Anonymous]

Thanks @Ashish_Mathur ! It seems you didn't update you file. When I download it from the link I still see the old version. Can you maybe re-upload?

[Ashish_Mathur · Accepted solution]

Try this link - https://1drv.ms/u/s!AnsG4LrWCkhUoIZAkJKIIR7B8sIm5g?e=7p5KJO.

[Ashish_Mathur]

You are welcome.  Still very confused.  How have you arrived at 135 - neither is that 80+5 nor is it 1015-930?

[Anonymous]

You are right! I changed the numbers while writing the post and forgot to update this one.  

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-jianboli-msft]

Hi @Anonymous,

Please try:

Sales.Rolling3M = 
var _a = DATESINPERIOD('Date_DIM'[Date ID],ENDOFMONTH(Date_DIM[Date ID]),-3,MONTH)
var _b = COUNTX(_a,[Date ID])
var _c = CALCULATE(SUM(FactTable[Volume in LT]), FactTable[DataType] = "Sales", _a)
return IF(_b=3,_c)

Forecast.Rolling3M = 
var _a = DATESINPERIOD('Date_DIM'[Date ID],ENDOFMONTH(Date_DIM[Date ID]),-3,MONTH)
var _b = COUNTX(_a,[Date ID])
var _c = CALCULATE(SUM(FactTable[Volume in LT]), FactTable[DataType] = "Forecast",_a)
return IF(_b=3,_c)

AbsDif = ABS([Forecast.Rolling3M]-[Sales.Rolling3M])

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.