Forum Discussion

grechill's avatar
grechill
Helper I
6 years ago

Rolling average for year end

Hello,

 

I'm trying to get a fixed measure to show me a sum of rolling average measure at the end of the year ignoring all other date filters.

 

I have tried various options like below, but none seems to give me the desired result.

 

 

Run Rate test = CALCULATE([Run Rate (interim) rolling average];FILTER(ALL(_Calendar);_Calendar[Date].[Year] = "2020"))

 

 

 

Here is my rolling average measure.

 

 

Run Rate (interim) rolling average = 
IF(
	ISFILTERED('_Calendar'[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('_Calendar'[Date].[Date])
	VAR __DATE_PERIOD =
		DATESBETWEEN(
			'_Calendar'[Date].[Date];
			STARTOFMONTH(DATEADD(__LAST_DATE; -12; MONTH));
			ENDOFMONTH(DATEADD(__LAST_DATE; 12; MONTH))
		)
	RETURN
		AVERAGEX(
			CALCULATETABLE(
				SUMMARIZE(
					VALUES('_Calendar');
					'_Calendar'[Date].[Year];
					'_Calendar'[Date].[QuarterNo];
					'_Calendar'[Date].[Quarter];
					'_Calendar'[Date].[MonthNo];
					'_Calendar'[Date].[Month]
				);
				__DATE_PERIOD
			);
			CALCULATE(SUM('Revenue'[Run Rate (interim)]); ALL('_Calendar'[Date].[Day]))
		)
)

 

 

 

Thanks in advance!

 

3 Replies