// Say you have a Date/Time dimension
// (let's call it Dates), and
// each month has a unique ID called
// MonthID (unique across all years).
// These ID's should be
// consecutive integers.
// Let your [Measure] be the measure
// that you want to calculate the
// 3M average for.
[Measure 3MA] =
// If you change __N to be 6
// then the code will calculate
// a 6-month average. You get
// the gist...
var __N = 3
var __lastVisibleDay =
LASTDATE( Dates[Date] )
var __lastMonthId =
MAX( Dates[MonthID] )
var __canGoBackNMonths =
// The first feasible date
// on which you could calculate
// the moving average is
// the last day of the Nth month
// counting from the start of
// the calendar.
var __firstJan =
CALCULATE(
MIN( Dates[Date] ),
ALL( Dates )
)
var __firstFeasibleDay =
EOMONTH( __firstJan, __N - 1 )
return
__LastVisibleDay >= __firstFeasibleDay
var __output =
if( __canGoBackNMonths,
var __NLastMonthsWithMeasure =
ADDCOLUMNS(
SELECTCOLUMNS(
GENERATESERIES(0, __N - 1),
"@Month", [Value]
)
"@Measure",
var __monthPeriod =
DATESINPERIOD(
Dates[Date],
DATEADD(
_lastVisibleDay,
(-1) * [@Month],
MONTH
),
-1,
MONTH
)
return
CALCULATE(
[Measure],
__monthPeriod
)
)
var __average =
AVERAGEX(
// If the value of [@Measure]
// for any of the months is BLANK,
// this value will be skipped by
// the AVERAGEX function. If you
// want to treat BLANKs as 0's
// and thus include them in the
// calculation, just add 0 to
// [@Measure].
__NLastMonthsWithMeasure,
[@Measure]
)
return
__average
)
return
__output
The code above works for any selection of dates. It calculates 3 months back starting from the most recent date visible in the context. So, you'll get numbers not only if you put months on the axis, but also for any date. The code is not simple because you have to remember that you can calculate such an average only when there are enough days to go back. If you fall off of the left edge of the calendar (Dates), you must return BLANK.
