Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 = IF( ISFILTERED('Date'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."), VAR __LAST_DATE = ENDOFMONTH('Date'[Date].[Date]) VAR __DATE_PERIOD = DATESBETWEEN( 'Date'[Date].[Date], STARTOFMONTH(DATEADD(__LAST_DATE, -3, MONTH)), __LAST_DATE ) RETURN AVERAGEX( CALCULATETABLE( SUMMARIZE( VALUES('Date'), 'Date'[Date].[Year], 'Date'[Date].[QuarterNo], 'Date'[Date].[Quarter], 'Date'[Date].[MonthNo], 'Date'[Date].[Month] ), __DATE_PERIOD ), CALCULATE( 'Closed Opportunities'[Average Days to Close], ALL('Date'[Date].[Day]) ) ) )
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.
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())) in 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.
David
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.
Steve
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
122 | |
109 | |
60 | |
55 |