Better Rolling Average

Greg_Deckler — Sat, 04 Feb 2023 15:44:18 GMT

Continuing with exploring alternatives to Power BI's default quick measures that don't involve the CALCULATE function, such as Better Running Total, Better Average per Category, Better Weighted Average per Category, Better Filtered Value, Better Sales from New Customers, and Year to Date Total and Year Over Year Change, this one tackles Rolling Average.

Power BI's Rolling Average quick measure returns something like this:

Value rolling average = IF( ISFILTERED('Dates'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), VAR __LAST_DATE = ENDOFMONTH('Dates'[Date].[Date]) VAR __DATE_PERIOD = DATESBETWEEN( 'Dates'[Date].[Date], STARTOFMONTH(DATEADD(__LAST_DATE, -3, MONTH)), __LAST_DATE ) RETURN AVERAGEX( CALCULATETABLE( SUMMARIZE( VALUES('Dates'), 'Dates'[Date].[Year], 'Dates'[Date].[QuarterNo], 'Dates'[Date].[Quarter], 'Dates'[Date].[MonthNo], 'Dates'[Date].[Month] ), __DATE_PERIOD ), CALCULATE(SUM('Table'[Value]), ALL('Dates'[Date].[Day])) ) )

Perhaps a better way:

Better Rolling Average = VAR __EndDate = MAX('Table'[Date]) VAR __3MonthsAgo = EOMONTH(__EndDate, -3) VAR __StartDate = DATE(YEAR(__3MonthsAgo), MONTH(__3MonthsAgo), 1) VAR __Table = SUMMARIZE( FILTER(ALL('Table'),[Date]>=__StartDate && [Date]<=__EndDate), 'Table'[Month], "__Value",SUM('Table'[Value]) ) RETURN AVERAGEX(__Table,[__Value])

And the video:

eyJrIjoiZjc5MDlhYjktOWYzZi00YzM3LWFlYWEtYWMyMGQyNzM4NGYwIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

Re: Better Rolling Average

HirotoStarParks — Tue, 21 Jan 2025 12:00:31 GMT

I have adapted your code to work on days:

```dax
DAILY_MOVING_AVERAGE =
// Previous day
VAR _End = MAX(CALENDAR[DATE]) -1
// Date for N months ago
VAR _Start = EDATE(_End, -MONTHLY_MOVING_AVERAGE[MONTHLY_MOVING_AVERAGE Value])
VAR _OutTable =
SUMMARIZE(
// Filter using all of the calendar dimension table
FILTER(ALL(CALENDAR),CALENDAR[DATE]>=_Start && CALENDAR[DATE]<=_End),
CALENDAR[DATE],
"__Value",[SUM_COUNT]
)
RETURN
AVERAGEX(_OutTable,[__Value])
```

topic Re: Better Rolling Average in Quick Measures Gallery

Better Rolling Average

Re: Better Rolling Average