I have worked through the forecasting tutorials but I have a problem that I am struggling with. I currently forecast by taking an average of the three rolling months for the month I am forecasting and the same three months the prior year and then apply the trend of those two periods to an average of sales in that month across the two years. The formula is below:
Stat Forecast 2YR = var SalesTY = CALCULATE([Sales Qty (units)]) Var SalesLY = CALCULATE([Sales Qty (units)],DATEADD(Dates[Date],-1,YEAR)) var Factor = CALCULATE([Rolling 3 Month Sales qty trend (%)]) Return DIVIDE(SalesTY+SalesLY,2,0)*Factor
This works and does what it needs to do but I need to create a comparison forecast where I apply the trend of the latest three months compared to the same three months in the prior year and extend that trend to all future months (so all months would have the same factor applied).
How is this different? Currently, to forecast July I apply the trend of last July rolling 3 months compared to the July rolling three months before that and apply it to an average of the last two Julys. The new method needs to apply the CURRENT three month rolling trend to all future months (so currently the would be Feb, Mar and Apr compared to Feb, Mar and Apr the year before). This would give me 1 factor such as 110% which would then be applied to all future months.
Based on the formula above I am unsure how to go about this - I was thinking a supporting table may be needed but any help would be great.