Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to Solution.
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
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
Thanks a lot! That did it.
Br
Johannes
User | Count |
---|---|
129 | |
72 | |
70 | |
58 | |
53 |
User | Count |
---|---|
190 | |
97 | |
67 | |
63 | |
54 |