Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

Rolling Average Past Current Month

I've used the new Quick Measures feature of Power BI to build a 3 month rolling average calculation and it's working well. The equation is displayed below. However, when I try to use this metric in a time series visualization, the calculations are displaying three months past the current month, but I'd like for the calculation to stop at the current month.


I've played around with the __DATE_PERIOD variable to no avail. My date filter for the page is set to show all dates in the current months or 12 months prior via a calculated column on the date table.


Is anyone aware of how I can get the visualization to end at the current month?


Average Days to Close Rolling Average = 
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),
                'Closed Opportunities'[Average Days to Close],

Rolling Average.PNG

Advocate III
Advocate III

Not sure if you're still having issues with this, but I'd like to share a hack fix for those landing here. I fixed this issue by filtering on the base data (in your example, this would be "Average Days to Close"). Set a visual-level filter to include only those items where Average Days to Close > 0, and you should get the extra dates cut off the end of the graph.


So long as all of your base data passes through the filter, you should be good.

Community Champion
Community Champion

I've had similar issues and have resorted to altering the query that creates the calendar to only have dates up to the current date or month.  In Edit Queries -> Advanced Editor, you will need to put in a line like the following:


1    #"Changed Type" = Table.TransformColumnTypes(#"Inserted Start of Month",{{"Date", type date}}),
2    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] <= Date.From(DateTime.LocalNow()))
3    #"Filtered Rows"

(remove the line numbers before using the code, they are there for me to explain what is going on)



Line 2 is the new code. #"Changed Type" refers to the name of line 1.  You will need to change your query to reflect this.

Line 3 in your query will likely have the same name as line 1.  You will need to rename line 3 to reflect line 2's name.


However, if you are building your date table in DAX, you can make a dynamic calendar thusly:


DateTab = CALENDAR("1/1/2010", TODAY())

Your starting date may vary.



Hope this helps.


Hi David,


I am building my date table via DAX and tried what you suggested, but it resulted in the same chart that I shared earlier. I've also got a filter on the page to only include dates in the current month or 12 months prior, but that doesn't seem to do the trick for the rolling average view.



I have found that some Time Intelligence functions don't play nicely with "incomplete" date tables.  I used the quick measure in an existing report of mine and I am experiencing the same behavior as you.  Since this is a preview feature this may be a bug or an unexpected feature - you should report it using the feedback button. 


I got it to at least calculate 0 for future months by removing 'Date'[Date].[QuarterNo] and 'Date'[Date].[MonthNo] from the SUMMARIZE function, but I can't get June and July to disappear.


Sorry I couldn't be more help.

Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.


Fabric certifications survey

Certification feedback opportunity for the community.