Moving average - rolling 7 days by country

[cocomy]

Hi All,

I am very begginer. I like to add rolling 7 days moving average by country on vizualization view.

I combine two tables into one , making more complicated I think... I imagine I can do it by using new measure function,

Appreciate if you can help me how to do it.

All the best,

cocomy

[v-yulgu-msft]

Hi @cocomy,

Please try this measure:

running average =
IF (
    DAY ( MAX ( 'Test Rolling'[Date] ) ) > 7,
    CALCULATE (
        AVERAGE ( 'Test Rolling'[Actual] ),
        FILTER (
            ALLEXCEPT ( 'Test Rolling', 'Test Rolling'[Country] ),
            'Test Rolling'[Date] >= DATEADD ( 'Test Rolling'[Date], -7, DAY )
                && 'Test Rolling'[Date] <= MAX ( 'Test Rolling'[Date] )
        )
    ),
    BLANK ()
)

Best regards,

Yuliana Gu

[cocomy · Accepted solution]

Hi

Thank you very  much for your advice.

It worked when all data in one table , however whe I tried merged table.. it did not work properly.

I will post another query for new question.

Thank you again!

coco