Add previous 4 years average using DAX

[paris]

Hi All,
I have monthly sales by city from 1 Jul 2017 to 1 Aug 2021. 
I would like to add 4 year average sales by city to my graph(like red line in the graph) Please help!

I tried to use similar dax I found in community , but did not work because...
It showed error when I typed Sales[Date].[Year]
I also do not know how to filter by city.... 

Measure =
AVERAGEX (
SUMMARIZE (
FILTER (
Sales,
YEAR ( Sakes[Date] )
>= YEAR ( TODAY () ) - 4
&& YEAR ( Sales[Date] ) < YEAR ( TODAY () )
),
Sales[Date].[Year]
),
CALCULATE ( SUM ( Sales[Sales] ) )
)

All the best,
Paris

[v-zhangti]

Hi, @paris 

For calculating the average, you can try the following two schemes.

1. Calculated column

Average =
CALCULATE (
    AVERAGE ( Sales[Sales] ),
    FILTER ( Sales, Sales[Month] = EARLIER ( Sales[Month] ) )
)

2. Measure

 Measure =
CALCULATE (
    AVERAGE ( Sales[Sales] ),
    FILTER ( ALL ( Sales ), Sales[Month] = MAX ( Sales[Month] ) )
)

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@paris 

Can you use the following measure?

AVERAGEX (
    SUMMARIZE (
        FILTER (
            all(Sales[Year]),
            Sales[Year] >= YEAR ( TODAY () ) - 4  && Sales[Year]  < YEAR ( TODAY () )
        ),
        Sales[Year]
    ),
    CALCULATE ( SUM ( Sales[Sales] ) )
)

[paris]

Hi Fowmy,

Thank you for your reply. I tried your measure but it shows same number as original sales except there is no data in 2021. 

I  wanted to add average for 2017 - 2020 only so I also tried below but the result is the same number as original sales except there is no data in 2021. 

AVERAGEX (
SUMMARIZE (
FILTER (
all(Sales[Year]),
Sales[Year] >= 2017 && Sales[Year] < 2021
),
Sales[Year]
),
CALCULATE ( SUM ( Sales[Sales] ) )
)

Thank you again and hope you can help me further. 

All the best,
Paris