Rolling 12 Month Average result used to Forecast

[Eduardo6]

Hello,

First time post.

Problem:

I have actual sales by month up to a point in time, lets say until March. I want to estimate future month sales using the result of the rolling average. For example, For April, I want to use the rolling 12 mo average up to the last data point (March). I would then like to forecast the future sales using the result of the rolling 12 mo average I came up with for April. So my May result would use the actuals up until March plus the rolling average for April. And so on.

[lbendlin]

If you create a line chart and put your dates on the x axis then you get the forecast for free in the "Analytics" section.

[Eduardo6]

@lbendlin 

Thanks for the quick response.

I was hoping to create a measure, as I will need these forecasted data points to derive other data.

[lbendlin]

@Eduardo6   how granular is your data? Is it on day or month level?

[Eduardo6]

@lbendlin  Month

[lbendlin]

Here is what I have so far:

I can show the actual value of the month as well as the 12 month average for the previous months.

LDoM = Last Day of Month . That is a column you need to add to your data so you can connect to a dates table

var a = that is the sum of the value of the 12 months prior to the "current"  month in the filter context.

What's still missing is 

- the logic to identify months without transactions.  the variable t  gets you halfway there, allowing to figure out how many prior months have actual data and how many need to be extrapolated

- the actual extrapolation logic.

Sorry about all the blurring, these numbers are not public.

In the chart the dark blue line are the actuals, the light blue line will be the forecast

[lbendlin]

@Eduardo6   Here's a slightly improved version that extrapolates both in the beginning and then end of data, but only 12 months out. Arguably this is sufficient as the forecast would not be reliable after that either.

Note that this does not do the recursion that you are after. I haven't figured out yet how to do that in DAX, or if it is even possible (via one of the iterators)

12MRolling = 
//find first day of current month
var m = SELECTEDVALUE(Dates[FDoM])
// make sure we don't count the TODAY() month
var tm = calculate(max(Dates[monthcode]),all(Dates),Dates[date]=TODAY())
//get data for last 12 months
var s = SUMMARIZE(filter(all('FactTable'),
   'FactTable'[Day]>=EDATE(m,-12) && 'FactTable'[Day]<m),
    Dates[monthcode],"val",sum('FactTable'[Value]))
// find out how many months actually have a full month of data
var n=countrows(filter(s,[val]>0 && Dates[monthcode]<>tm))
// get the total value
var t = sumx(filter(s,[val]>0 && Dates[monthcode]<>tm),[val])
// get the weighted average
return t/n

Looks pretty good to me, even the uptick at the end makes sense for my data.

[Eduardo6]

@lbendlin 

In your formula you have a day column in your fact table, I don't so I used the 'FactTable'[Date] that I do have. I don't think that makes a difference but I thought I would ask. Again, my data is already in monthly form. 

'FactTable'[Day]>=EDATE(m,-12) && 'FactTable'[Day]<m)

If that isn't really a factor then I don't think we are getting what I'm after yet. Thank you so much for your help up to this point. 

[lbendlin]

@Eduardo6  Yes, you just need the date for the date math.  So in your data you need to add a column that for each month lists a date in that month, for example the first day of the month.