I'll show you how to do a 7-day rolling average of any measure but this will be on the assumption that your model is correct (currently it is not, please refer to the link about star-schema I talked about before). For this to work, you have to have a Date table (call it 'Event Date') that'll be connected to your fact table on the 'Event Date'[Date] field (joining to Fact[EventDate]).
// Please remember that in a correct model
// slicing and dicing is only ever performed
// by selections from dimensions, never
// fact tables. The latter must always be
// hidden (at least all their columns must be,
// measures are OK to remain exposed as long
// as they are not helper measures that should
// indeed be hidden as well).
[7D Rolling Avg] =
var __daysCountRequired = 7
// First, we have to check
// if there are at least 7
// days available to calculate
// the average.
var __lastVisibleDay = max( 'Event Dates'[Date] )
// Please make sure that 'Event Dates' is
// marked as a Date table in the model.
var __periodToAverageOver =
DATESINPERIOD(
'Event Dates'[Date],
__lastVisibleDay,
-(__daysCountRequired - 1),
DAY
)
// You have to check this condition
// because the date table starts
// on some day and if you are too close
// to it, there will not be 7 full days
// available to calculate the average.
var __shouldCalculate =
COUNTROWS( __periodToAverageOver ) = __daysCountRequired
// Please bear in mind that if for a
// particular day the value of the measure
// [Your Measure] is BLANK, this value
// is not taken into account. In other words,
// the average of 1, blank, 2 is (1 + 2)/2.
// If you want to treat blank as 0 so that
// the average of 1, blank, 2 is (1 + 0 + 2) / 3
// then add 0 to the measure under AVERAGEX.
var __average =
if( __shouldCalculate,
AVERAGEX(
__periodToAverageOver,
[Your Measure]
)
)
return
__average
Of course, you could also want the 7D average but only for those days that do have a non-blank measure. If this is the case, you could use this code:
[7D Rolling Avg] =
var __daysCountRequired = 7
var __lastVisibleDay = max( 'Event Dates'[Date] )
// The assumption here is that if there is
// an entry in the FactTable for a particular
// day, it means the measure you're trying to
// average is not blank for this day.
var __periodToAverageOver =
TOPN(__daysCountRequired,
SUMMARIZE(
CALCULATETABLE(
FactTable,
'Event Dates'[Date] <= __lastVisibleDay
),
'Event Dates'[Date]
),
'Event Dates'[Date],
DESC
)
var __shouldCalculate =
COUNTROWS( __periodToAverageOver ) = __daysCountRequired
var __average =
if( __shouldCalculate,
AVERAGEX(
__periodToAverageOver,
[Your Measure]
)
)
return
__average
