Create forecast using moving average of recent actuals

[BRV]

I want to create a forecast of sales using the average of actual sales over the past several weeks as the forecast. See below for an example of the data I have and what I'm trying to acccomplish. I have measures for Daily Sales, Sales Lagged 1 Week, and the average of the first 2 measures. What I want to create is a measure that yields the results in the Forecast column. Each value in this column is the most recent value for that day (Mon, Tues, Wed, etc.) from the Average column. I want to extend the forecast column through the last date in the my Calendar table which is the end of the current year. Any help will be greatly appreciated.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

Daily Sales: =

SUM( Sales[Sales] )

Sales Lagged one week: =

VAR currentdate =

MAX ( Dates[Date] )

RETURN

IF (

NOT ISBLANK ( [Daily Sales:] ),

CALCULATE ( [Daily Sales:], Dates[Date] = currentdate - 7 )

)

Average: =

VAR salestable =

ADDCOLUMNS ( VALUES ( Dates[Date] ), "@value", [Daily Sales:] )

VAR laggedtable =

ADDCOLUMNS ( VALUES ( Dates[Date] ), "@value", [Sales Lagged one week:] )

VAR uniontable =

UNION ( salestable, laggedtable )

RETURN

IF (

[Daily Sales:] && [Sales Lagged one week:],

AVERAGEX ( uniontable, [@value] )

)

Forecast: =

VAR currentday =

WEEKDAY ( MAX ( Dates[Date] ) )

VAR lastsalesdate =

CALCULATE ( MAX ( Sales[Date] ), REMOVEFILTERS () )

VAR newtable =

FILTER (

SELECTCOLUMNS (

ADDCOLUMNS (

DATESINPERIOD ( Dates[Date], lastsalesdate, -7, DAY ),

"@avg", [Average:]

),

"@weekday", WEEKDAY ( Dates[Date] ),

"@average", [@avg]

),

[@weekday] = currentday

)

RETURN

IF ( MAX ( Dates[Date] ) > lastsalesdate && HASONEVALUE(Dates[Date]), MAXX ( newtable, [@average] ) )

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

Daily Sales: =

SUM( Sales[Sales] )

Sales Lagged one week: =

VAR currentdate =

MAX ( Dates[Date] )

RETURN

IF (

NOT ISBLANK ( [Daily Sales:] ),

CALCULATE ( [Daily Sales:], Dates[Date] = currentdate - 7 )

)

Average: =

VAR salestable =

ADDCOLUMNS ( VALUES ( Dates[Date] ), "@value", [Daily Sales:] )

VAR laggedtable =

ADDCOLUMNS ( VALUES ( Dates[Date] ), "@value", [Sales Lagged one week:] )

VAR uniontable =

UNION ( salestable, laggedtable )

RETURN

IF (

[Daily Sales:] && [Sales Lagged one week:],

AVERAGEX ( uniontable, [@value] )

)

Forecast: =

VAR currentday =

WEEKDAY ( MAX ( Dates[Date] ) )

VAR lastsalesdate =

CALCULATE ( MAX ( Sales[Date] ), REMOVEFILTERS () )

VAR newtable =

FILTER (

SELECTCOLUMNS (

ADDCOLUMNS (

DATESINPERIOD ( Dates[Date], lastsalesdate, -7, DAY ),

"@avg", [Average:]

),

"@weekday", WEEKDAY ( Dates[Date] ),

"@average", [@avg]

),

[@weekday] = currentday

)

RETURN

IF ( MAX ( Dates[Date] ) > lastsalesdate && HASONEVALUE(Dates[Date]), MAXX ( newtable, [@average] ) )

[BRV · Accepted solution]

This was extremely helpful - exactly what I was looking for. Thank you for the quick response.