Rolling Average Quick Measure Incorrectly Calculating the Future

[sbstern]

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 line (monthly revenue/customer) ends in September 2017, but the 12-month rolling average somehow exteds through September 2018.  How do I get it to display a true 12-month rolling average--which wouldn't project into the future?

Here's the Quick Measure set-up that got this result. The date column in the 'Avg Revenue' table only goes through the current date (I've double checked). The ('Calendar'[Date]) column goes through June 2018, but I've filtered the entire report by an [IsBeforeToday?] column that filters out anything after the current date.

 Please help!

[jmf2244]

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

It worked for me.

[ErnoNykanen]

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.

[kubo]

I solved this isue by filtering out the future values with a following measure

IsRecord = IF(max(Table1[value])<>BLANK();1)

Then set a filter only for values where IsRecord is not blank. Table1[value] is your base value in rolling average. Enjoy.

[v-haibl-msft]

@sbstern

You can try to set the End date in Format - X-Axis as following screenshot.

Best Regards,
Herbert

[sbstern]

Thanks, but I don't want to have to update it each month. It always end at the current month. It also makes me suspect the whole calculation since you can't have a 12-month rolling average in the future (unless it's a projection).

[Anonymous]

Anyone solved this yet?  What I (and I believe the others in this forum) am trying to creat is technically a 'Trailing Average' and not a Rolling Average.  A Trailing Average ceases on the last date on which data exists.  A rolling average can continue on forever (even though it will go to zero for dates that are far enough into the future.  There is no 'trailing avereage' quick measure, so I have been trying to make the Rolling Average Quick Measure bend to my needs.  I'm close, but this has been vexing me as well since I have been using the Rolling Average Quick Measure.  It's tough to explain to data consumers why there is trailing average data for dates that have yet to record any data and absolutely creates doubt in their minds as to the validity of the underlying calculation (even though it is technically correct).  I believe it has to do with the fact that the LASTDATE call in the quick measure is returning the last date of the year (12/31/2018 in my case) as opposed to the last date in the dataset (usually within a day or two of today).  I too do not want to handle via limiting the X-axis in graphs/visualizations as I am constantly changing the Rolling Period for analysis (7, 14, 21, 28 days) and would need to reset the X-axis every time.  I'd like to find a way to 'force' the trailing average calculation to stop at the latest date in the data set and not that latest date for which the calculation is 'zero'.  Any help would be greatly appreciated.

[Anonymous]

In my specific case I was able to address this indirectrly.  I added a measure that COUNTs the metric that is being displayed in the rolling avg.  Then I added an advanced filter for each visualization for that COUNT measure that will only show items of that metric that are over zero.  This trims the date automatically to the last date with real data in the set.  I hope it helps others.