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

Need Help

3 Replies

  • 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



  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    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.