Rolling Average Past Current Month

[steveplatz]

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

[andrewbrick]

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]

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

[steveplatz]

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

[dedelman_clng]

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.