Forum Discussion

nerra's avatar
nerra
Helper II
5 years ago
Solved

Rolling Average showing future dates

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
 

 

Need Help DAX
Rolling AVG
  • nerra's avatar
    nerra
    5 years ago

    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

1 Reply

  • nerra's avatar
    nerra
    Helper II
    5 years ago

    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