Rolling average on calculated table

Question

Hi!&nbsp;I have created a rolling average using 'quick measure'. Following dax is used:Opened_cases_on_date rolling average = IF(&nbsp;&nbsp;&nbsp;&nbsp;ISFILTERED('Dates'[Date]);&nbsp;&nbsp;&nbsp;&nbsp;ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.");&nbsp;&nbsp;&nbsp;&nbsp;VAR __LAST_DATE = LASTDATE('Dates'[Date].[Date])&nbsp;&nbsp;&nbsp;&nbsp;RETURN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AVERAGEX(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DATESBETWEEN(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Dates'[Date].[Date];&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DATEADD(__LAST_DATE; -30; DAY);&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;__LAST_DATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;);&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE(AVERAGE('Dates'[Opened_cases_on_date]))&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;))&nbsp;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.&nbsp;Is there a workaround?

mariusz · Accepted Answer

Hi&nbsp;Anonymous&nbsp;Yes there is, please see the belowOpened_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]))
    )&nbsp;You probobly want to mark your&nbsp;'Dates' table as a 'date table' this will be requierd if you ever wont write any built in Time Inteligence&nbsp; DAX Functions.Regards,MariuszIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Rolling average on calculated table

1 Reply

Related content

Rolling 12M Average measure

Rolling accumulation

3 Day Rolling Average - DAX

Rolling accumulation

Conditional Rolling Average