Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
JohannesM
Helper I
Helper I

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

JohannesM_0-1666857750715.png

What I need so that I can plot in chart:

JohannesM_1-1666857826271.png

 

Thanks in advance!

 

Br

Johannes

 

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

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:

vluwangmsft_0-1666948533386.png

 

 

Best Regards

Lucien

View solution in original post

2 REPLIES 2
v-luwang-msft
Community Support
Community Support

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:

vluwangmsft_0-1666948533386.png

 

 

Best Regards

Lucien

Thanks a lot! That did it.

 

Br

Johannes

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.