// Your Calendar table should
// have all days covering all
// full years that are present
// in your data. Then, you should
// connect the field [Date] to
// the field of THE SAME TYPE in
// your fact table. The fact that
// you collect data monthly has nothing
// to do with the fact that you'll
// be joining the two tables on a date,
// not on a month. In the fact table
// you just mark a month as the first
// date of the relevant month and join on it
// to the calendar. Then, if you don't
// really deal with days, you should
// hide days and only leave the relevant
// pieces of time exposed (months, years...).
// Of course, Calendar must be marked as
// a date table in the model. The code
// below will work on assumption that
// you've hidden days in the calendar.
// Once you have the above, moving
// avg is easy to create.
[Num Of Visits - 12M Avg] =
// If you want to see the avg per shop,
// just drop shops on the canvas.
var __lastVisibleDate = MAX( Calendar[Date] )
var __veryFirstYearInCalendar =
CALCULATETABLE(
MIN( Calendar[Year] ),
ALL( 'Calendar' )
)
var __lastEffectiveDateInFirstYear =
// Only if the last visible date is
// on or after this date, you're able
// to calculate the 12M average. Otherwise,
// there will not be enough months to
// average over.
date( __veryFirstYearInCalendar, 12, 1 )
var __canCalculate =
__lastVisibleDate >= __lastEffectiveDateInFirstYear
return
if( __canCalculate,
var __monthsToAvgOver =
CALCULATETABLE(
// UniqueMonthId must be a unique int
// assigned to each unique month. No
// two months across the entire calendar
// can have the same ids.
VALUES( Calendar[UniqueMonthId] ),
DATESINPERIOD(
Calendar[Date],
__lastVisibleDate,
-12,
MONTH
)
)
return
AVERAGEX(
__monthsToAvgOver,
CALCULATE(
SUM( FactTable[Number Of Visits] ),
ALLEXCEPT(
'Calendar',
'Calendar'[UniqueMonthId]
)
)
)
)
If you also deal with single days, you should wrap this measure in a guard clause that will calculate this only when full months are visible, not just dates or a selection of dates that does not cover full months.
