Forum Discussion

sbstern's avatar
sbstern
Advocate II
8 years ago

Rolling Average Quick Measure Incorrectly Calculating the Future

This is the first time I've tried using the new Rolling Average Quick Measure and I'm out of ideas as to what's wrong. I'm trying to get a 12-month rolling average of our revenue/customer. The blue l...
Data Modeling
Reports
jmf2244's avatar
jmf2244
New Member
7 years ago

I modified the Quick Measure based of Anonymous 's and your suggestions. This is the final code:

 
It worked for me.
ErnoNykanen's avatar
ErnoNykanen
Frequent Visitor
6 years ago

This one worked for me as well. The key is to add another IF with MAX statement on your filtering column.

Actual EUR rolling average = 
IF(
	ISFILTERED('tCalender'[Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    IF(
        MAX(tCalender[Month Difference]) < 0,
    VAR __LAST_DATE = ENDOFMONTH('tCalender'[Date].[Date])

I calculated the difference between date in calender table and current date in the Month Difference column and check that it's always less than 0 as I don't want to calculate the average with partial data mid-month.