Forum Discussion

NicoleApple's avatar
NicoleApple
New Member
4 years ago
Solved

7 day moving average

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's avatar
    Jihwan_Kim
    4 years ago

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

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
    Anonymous
    Not applicable
    4 years ago

    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