Calculate rolling average on data that precedes the date range of a visual

[andrewbrick]

I have the following rolling average function as a measure in a report (made from a quick measure):

ON_TIME rolling average = 

VAR __LAST_DATE = ENDOFMONTH(DateTable[Date].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
DateTable[Date].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, -11, MONTH)),
__LAST_DATE
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('otp_data'),
DateTable[Date].[Year],
DateTable[Date].[QuarterNo],
DateTable[Date].[Quarter],
DateTable[Date].[MonthNo],
DateTable[Date].[Month]
),
__DATE_PERIOD
),
CALCULATE(SUM('otp_data'[ON_TIME])+SUM(otp_data[NEW_ET]), ALL(DateTable[Date].[Day]))

Let's say I have three years worth of data.  I've got a visual set up with a date hierarchy as the x-axis (as is required), and it normally shows the data by month.  I want the visual to show only the latest two years' worth of data.  I can make that happen, but the rolling average calculation always starts with the first data point shown in the visual.  So the first data point and the rolling average for that point will always be equal.  This is not accurate, based on what exists in the underlying data.  The 12-month rolling average for month 13 in the data should average the previous 12 months of data, even if month 13 is the left-most data point in the visual.

What I want is for the rolling average function to access data farther back in time than what is being shown in the visual.  I have tried using ALL() and ALLSELECTED() around the date column in the DATESBETWEEN() function that defines the __DATE_PERIOD variable, but DAX is throwing an error.

Is there a way to make this happen?

[Anonymous · Accepted solution]

Hi @andrewbrick,

I'd like to suggest you to use date function to manually filter date ranges instead to use time intelligence functions.

Time Intelligence "The Hard Way" (TITHW)

Time intelligence functions look simple to use, but it hard to deal with complex scenario.(e.g. nested calculation, calculation grouped result)

If above not help, can you please share a pbix file with some sample data to test and coding formula? You can upload it to onedrive or google drive and share link here.

Regards,

Xiaoxin Sheng