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

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

JohannesM's avatar
JohannesM
Helper I
3 years ago

Thanks a lot! That did it.

 

Br

Johannes