Rolling average on calculated table

[Anonymous]

Hi!

I have created a rolling average using 'quick measure'. Following dax is used:

Opened_cases_on_date rolling average =

IF(

    ISFILTERED('Dates'[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('Dates'[Date].[Date])

    RETURN

        AVERAGEX(

            DATESBETWEEN(

                'Dates'[Date].[Date];

                DATEADD(__LAST_DATE; -30; DAY);

                __LAST_DATE

            );

            CALCULATE(AVERAGE('Dates'[Opened_cases_on_date]))

        )

)

To do this, I needed to accept Powerbi's date hierarchy. However, in my report I just want to show the date in 1 field (ex. Monday May 17 2019). When I go to my 'Values' pane, click on date and select 'Date' instead of 'Date Hierarchy', the date is shown in the correct way but the rolling average gives an error.

Is there a workaround?

[Mariusz · Accepted solution]

Hi @Anonymous 

Yes there is, please see the below

Opened_cases_on_date rolling average =
VAR __LAST_DATE = LASTDATE('Dates'[Date])
RETURN
    AVERAGEX(
        DATESBETWEEN(
            'Dates'[Date];
            DATEADD(__LAST_DATE; -30; DAY);
            __LAST_DATE
        );
        CALCULATE(AVERAGE('Dates'[Opened_cases_on_date]))
    )

You probobly want to mark your 'Dates' table as a 'date table' this will be requierd if you ever wont write any built in Time Inteligence  DAX Functions.

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.