Forum Discussion

JohannesM's avatar
JohannesM
Helper I
3 years ago
Solved

Rolling average without date table

Hi,

I have a table with sales and I want to be able to plot the rolloing average in a chart. I now have a DAX measure for average last 3 years but only as a number seen from today and I can't use that for a chart with time on the x-axis. 

 

What I use for non dynamic average:

Average last 3 years =

    AVERAGEX(

        FILTER(

            'table1',

            'table1'[id] > MAX('table1'[id]) -6 &&

            'table1'[id] <= MAX('table1'[id])

        ), 'table1'[sales]

    )

 

My table (table1):

What I need so that I can plot in chart:

 

Thanks in advance!

 

Br

Johannes

 

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    3 years ago

    Hi JohannesM ,

    Pls use the below dax to create a new column:

    Average last 3 years = IF(table1[year]-MIN(table1[year])>=2,

   FORMAT( AVERAGEX(

        FILTER(

            'table1',

            'table1'[id] > EARLIER('table1'[id]) -6 &&

            'table1'[id] <= EARLIER('table1'[id])

        ), 'table1'[sales]

    ),".0"),BLANK())

    Output result:

     

     

    Best Regards

    Lucien

2 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    3 years ago

    Hi JohannesM ,

    Pls use the below dax to create a new column:

    Average last 3 years = IF(table1[year]-MIN(table1[year])>=2,

   FORMAT( AVERAGEX(

        FILTER(

            'table1',

            'table1'[id] > EARLIER('table1'[id]) -6 &&

            'table1'[id] <= EARLIER('table1'[id])

        ), 'table1'[sales]

    ),".0"),BLANK())

    Output result:

     

     

    Best Regards

    Lucien