7 day moving average

Hi NicoleApple,

It seems like a DAX syntax issue that loses the right brackets at the end of your formula.  You can add it to the end to fix this issue.

7 day moving average =
CALCULATE (
    SUM ( 'PCR TEsting Data'[Total Volume of Tests (Excl. work in progress or tests referred out) (No.)] ) / 7,
    DATESINPERIOD (
        'PCR TEsting Data'[Relevant Date],
        LASTDATE ( 'PCR TEsting Data'[Relevant Date] ),
        -7,
        DAY
    )
)

If the above expression does not work, you can also try to use the date function to manually calculate the moving average.

7 day moving average =
VAR currDate =
    MAX ( 'PCR TEsting Data'[Relevant Date] )
VAR prevDate =
    DATE ( YEAR ( currDate ), MONTH ( currDate ), DAY ( currDate ) - 7 )
VAR summary =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'PCR TEsting Data' ),
            [Relevant Date] >= prevDate
                && [Relevant Date] <= currDate
        ),
        [Relevant Date],
        "Total", SUM ( 'PCR TEsting Data'[Total Volume of Tests] )
    )
RETURN
    AVERAGEX ( summary, [Total] )

Regards,

Xiaoxin Sheng