Forum Discussion

paris's avatar
paris
Helper V
4 years ago

Add previous 4 years average using DAX

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 us...
Need Help
Fowmy's avatar
Fowmy
Super User
4 years ago

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's avatar
paris
Helper V
4 years ago

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