Forum Discussion

nerra's avatar
nerra
Icon for Helper II rankHelper 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...
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
nerra's avatar
nerra
Icon for Helper II rankHelper 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