cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Super User I
Super User I

Re: Rolling Average Past Current Month

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




Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Rolling Average Past Current Month

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

Highlighted
Super User I
Super User I

Re: Rolling Average Past Current Month

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.




Proud to be a Super User!




Highlighted
Helper II
Helper II

Re: Rolling Average Past Current Month

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.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors