7 day moving average

[NicoleApple]

I am trying to urgently calculate a 7 day moving a average but im having issues. I could do it in Excel but i want to use Power BI to present the data instead and my DAX is a little rusty as i havent used it for 4 years!

I have created a formula as follows:

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

) but it is coming up with an error saying "Argument 3 in Calculate function is required. Am i missing something here? 

[Jihwan_Kim · Accepted solution]

Hi,

I do not fully understand the DAX formula, but I think it has to have one more  )  at the end of the formula.

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
    )
)

[Anonymous]

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