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
Icon for Community Support rankCommunity 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.