Power BI Rolling Average

1
votes

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])
            )
        )
)

enter image description here

2
powerbidax

2 Answers

2
votes

In order to limit what is displayed within your chart, you need to filter the applicable date field so it only displays the dates you desire. In this case, you only want it to include dates <= today.

In order to automatically filter it when it is refreshed, I typically add a custom DAX column to the date table that I can filer on. In this case it would be something along the lines of:

excludeFutureDatesInd = 'Date'[Date] <= TODAY()

You can then add a visual, page, or report filter selecting all dates where [excludeFutureDatesInd] = True.

0
votes

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.