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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
steveplatz
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 = 
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])
            )
        )
)

Rolling Average.PNG

4 REPLIES 4
andrewbrick
Advocate IV
Advocate IV

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.

dedelman_clng
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()))
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.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.