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 f...
Need Help
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.