Better Rolling Average 11-10-2022 07:26 AM - last edited 02-04-2023 07:44 AM

[Greg_Deckler]

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