Forum Discussion
Last 3 months calculation not working
Hi Team,
I am working on logic for last 3 months logic. after SKU all are measures.
"V4Cast ESF WD9 Lag3" is forecast and actual detail table is actual.
"DVMI_Forecast_error" is Forecast - Actuals.
"DVMI_Forecast_error_ABS" is Absolute value of DVMI_Forecast_error.
"Total Last 3 Months" non absolute Last 3 months calculation of every month for DVMI_Forecast_error and its working accurate.
when i apply same logic on "ABS Total Last 3 Months" by using "DVMI_Forecast_error_ABS" its not working.
logic used
ABS Total Last 3 Months =
CALCULATE(
[DVMI_Forecast_Error_ABS],
DATESINPERIOD(
'DimDate'[Date],
LASTDATE('DimDate'[Date]),
-3,
MONTH
)
)
1 Reply
The issue is that ABS of a sum is not the same as the sum of ABS. When you wrap the measure in CALCULATE with DATESINPERIOD, the whole 3-month window becomes the filter context, so DAX first sums Forecast and Actuals across all 3 months and then takes the absolute value once at the end. That gives you the absolute of the net error, not the sum of the monthly absolute errors.
To fix it, iterate month by month and sum the per-month ABS:
ABS Total Last 3 Months =
SUMX(
SUMMARIZE(
DATESINPERIOD('DimDate'[Date], LASTDATE('DimDate'[Date]), -3, MONTH),
'DimDate'[Year],
'DimDate'[MonthNumber]
),
[DVMI_Forecast_Error_ABS]
)
If your date table has a YearMonth key column, you can use that single column inside SUMMARIZE instead. The key point is that ABS must be computed per month before adding, which is what your non-absolute Total Last 3 Months is effectively doing already since there is nothing to flip signs on.
If this helped, a thumbs up and accepting the solution would be appreciated.
Best,
Shai Karmani
