Rolling Average showing future dates

[nerra]

HI all,

I have a rolling average metric defined on top of a custom date table which should show only rolling 4 weeks (28 days):

The metric is defined as:

1. Average of Open To Close rolling average 2 =

IF(

    ISFILTERED('Date'[Week_Start_Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __LAST_DATE = LASTDATE('Date'[Week_Start_Date].[Date])

    RETURN

        AVERAGEX(

            DATESBETWEEN(

                'Date'[Week_Start_Date].[Date],

                DATEADD(__LAST_DATE, -28, DAY),

                __LAST_DATE

            ),

            CALCULATE(AVERAGE('ams_auto v_closed_ticket'[Open To Close]))

        )

)

The Date table is defined as:

Date =

ADDCOLUMNS (

CALENDAR (DATE(1990,1,1), DATE(2025,12,31)),

"Date_ID", FORMAT ( [Date], "YYYYMMDD" ),

"Week_Start_Date", DATEVALUE([Date] - WEEKDAY([Date],2) +1),

"Year", YEAR ( [Date] )

)

So my question is, how can I limit the Rolling Average metric to not show dates in the future?

Kind regards,

Nerra

[nerra · Accepted solution]

I've managed to find a partial solution by making the upper filter..

VAR __Calc =

IF(

    ISFILTERED('Date'[Week_Start_Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __LAST_DATE = LASTDATE('Date'[Week_Start_Date].[Date])

    RETURN

        AVERAGEX(

            DATESBETWEEN(

                'Date'[Week_Start_Date].[Date],

                DATEADD(__LAST_DATE, -28, DAY),

                __LAST_DATE

            ),

            CALCULATE(AVERAGE('ams_auto v_closed_ticket'[Open To Close]))

        )

)

RETURN

IF(MAX('Date'[Week_Start_Date].[Date])>TODAY(),BLANK(),__Calc)

Thanks,

N