cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

What I need so that I can plot in chart:

Br

Johannes

1 ACCEPTED SOLUTION
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:

Best Regards

Lucien

2 REPLIES 2
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:

Best Regards

Lucien

Helper I

Thanks a lot! That did it.

Br

Johannes

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors