Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
88 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
131 | |
110 | |
96 | |
70 | |
67 |