cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 3
Highlighted
Super User IX
Super User IX

Re: Rolling average for year end

I did not get it completely. But we have rolling like

Rolling 12 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))

or

YTD = CALCULATE(Average(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User IX
Super User IX

Re: Rolling average for year end

Tough to say without sample data to test. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Perhaps: 

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Rolling average for year end

Hi @amitchandak ,

 

Those measures would be limited to YTD and would not roll forward, if necessary. They are not usable for forecasting. Im interested in the forecasting in particular. 

I would like that Power BI displays me an END of year forecast no matter what filters Im applying in date slicer. I forecast all upcomming months by rolling avg, then make date slicers invalid with ALL() funcion and then apply year filter. Unforunately, Power BI is producing error, however formula looks logically correct to me. Any ideas where the mistake is?

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors