Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Getting a rolling 3 months forecast accuracy

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 IDDate IDVolume in LTDataType
AJan 22100Sales
AJan 22110Forecast
AFeb 22105Sales
AFeb 22110Forecast
AMar 22110Sales
AMar 22110Forecast
AApr 22120Sales
AApr 22110Forecast
AMay 22100Sales
AMay 22110Forecast

 

Date ID3M Sales3M ForecastAbs Diff 3MFCA
Mar 223153301595,2%
Apr 22335330598,5%
May 223303300100%

 

 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 ID3M Sales3M ForecastAbs Diff 3MFCA
Jan 221001101090%
Fen 22205220595,2%
Mar 223153300100%
Apr 223353301091,7%
May 223303301090%

 

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! 

 
Need Help

11 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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 IDDate IDVolume in LTDataType
    BJan 22220Sales
    BJan 22200Forecast
    BFeb 22200Sales
    BFeb 22200Forecast
    BMar 22280Sales
    BMar 22200Forecast
    BApr 22200Sales
    BApr 22200Forecast
    BMay 22240Sales
    BMay 22200Forecast

     

    The expected result is this:

    Product IDDate IDRolling 3M SalesRolling 3M FCAbs DifFC Accuracy
    AMar 2231533015 
    BMar 22700600100 
    TotalMar 22101593011588,7%
    AApr 223353305 
    BApr 2268060080 
    TotalApr 2210159308586,7%
    AMay 223303300 
    BMay 22720600120 
    TotalMay 22105093012088,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's avatar
      Ashish_Mathur
      Super User
      3 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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?

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    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.