Using current trend to forecast sales

DAX DAX Calculations
1

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.

Thanks

2

This is a really interesting scenario you’re looking to solve.

I would do this slightly differently.

First I would create a calculated column which works out which day of the quarter each date is.

You can do that with this formula

Quarter Day = ( Dates[Date] - STARTOFQUARTER( Dates[Date] ) ) + 1

Then I would create a measure that used this logic.

Quarterly Forecast = 
CALCULATE( [Total Sales],
    FILTER( ALL( Dates ),
        Dates[Year] = 2015 &&
        Dates[QuarterOfYear] = 3 &&
        Dates[Quarter Day] = MIN( Dates[Quarter Day] ) ) )

You see I’ve hard coded the year and quarter. You would need to customize this somehow.

You would just sub in you current calculation where I have total sales also.

This patter will just iterate through the same number every single quarter continuously.

image
image.png1188×546 45.3 KB

See how you go with these techniques.

Chrs

3

Thank you very much for this - sorry it has taken so long to reply, I have been working through a separate part of my report. I am now utilising the above technique to create reactivity settings for my forecast (very reactive - use the last 3 month trend, low reactivity - use the last 9 month trend). I am currently working to get these to work off buttons in my report - seems to be going ok so far…