Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
This measure calculates a rolling average. It takes a set number of periods before and after the current filter context and does an average over those values.
Rolling average
Calculate the average of the value for a set number of periods before and after the filtered date
Name: Base value
Tooltip: The value you want to average
Type: Numerical field / measure
Name: Date
Tooltip: The dates over which you want to calculate the average
Type: Date field
Name: Period
Tooltip: The time periods between which you want to average
Type: Enum, from Days, Months, Quarters, Years
Name: Periods before
Tooltip: The number of periods to average before each date
Type: Integer
Name: Periods after
Tooltip: The number of periods to average after each date
Type: Integer
{Base value} rolling average =
IF(
ISFILTERED({Date}),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),
VAR __LAST_DATE =
LASTDATE({Date}.[Date])
RETURN
AVERAGEX(
DATESBETWEEN(
{Date}.[Date],
DATEADD(__LAST_DATE, {Periods Before}, {Period}),
DATEADD(__LAST_DATE, {Periods After}, {Period}),
),
CALCULATE({Base value})
)
)
eyJrIjoiN2ExNWFjOGItN2IwNC00MTkzLWIyMDAtYWI4YjdiMjlhMWQ2IiwidCI6IjNlN2ZjNjM1LTkxOTAtNDFmMC04MDZiLWI4OWIwZmJkNzU5ZSIsImMiOjF9
I tried this measure and it worked fine as long as I didn't try to edit the DAX expression. As soon as I did, I got a syntax error as described here. This makes for a very confusing experience and I would suggest to either fix the cause of the syntax error (seems unlikely) or to insert a space before the commata after {Periods Before} and {Periods After}.
Thanks for sharing the details for the rolling average quick measure.
I get an incorrect result when I use a base value requires a different date field in the calculation.
For example, I'd like to calculate the rolling average for my base value 'FoundationCount' below. My main date table is CohortDate[Date] which points to several date fields in Cohort_Roster table including Cohort_Roster[FoundationsDate]. The relationship to Cohort_Roster[FoundationsDate] is inactive by default so I activate it when needed below.
FoundationsCount := CALCULATE( countx(Cohort_Roster,Cohort_Roster[FoundationsDate]), USERELATIONSHIP(CohortDate[Date],Cohort_Roster[FoundationsDate] ))
Can you advise any edits to the DAX code above where I could add 'USERELATIONSHIP' to pick up the correct date values?
Thanks.
Both files are png - can you please share .pbix?
Thanks