Rolling Average

Moderator
10073 Views
Highlighted
Moderator
Posts: 208
Registered: ‎06-23-2015

Rolling Average

[ Edited ]

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. 

 

Name:

Rolling average

Description:

Calculate the average of the value for a set number of periods before and after the filtered date 

Parameters:

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

 

DAX:

{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})
  )
)

 

Regular Visitor
Posts: 27
Registered: ‎02-20-2017

Re: Rolling Average

@WillT can you share a sample for this example. Thanks. 

Frequent Visitor
Posts: 2
Registered: ‎04-09-2017

Re: Rolling Average

Both files are png - can you please share .pbix?

Thanks

Frequent Visitor
Posts: 3
Registered: ‎08-17-2017

Re: Rolling Average

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.

Frequent Visitor
Posts: 10
Registered: ‎02-09-2018

Re: Rolling Average

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}.