Forum Discussion
Calculate Moving Average
Hi,
I have data which looks like:
| Site | Date | Value | Moving Average |
| London | 01-01-2022 | 0.1 | 0.1 |
| London | 28-01-2022 | 0.2 | 0.15 |
| London | 01-02-2022 | 0.3 | 0.2 |
| London | 01-03-2022 | 0.6 | 0.3 |
| London | 01-04-2022 | 0.12 | 0.48 |
| Bradford | 01-01-2022 | 1 | 1 |
| Bradford | 25-01-2022 | 3 | 2 |
| Bradford | 11-02-2022 | 1 | 1.6 |
| Bradford | 01-03-2022 | 5 | 2.5 |
| Bradford | 16-04-2022 | 10 | 4 |
I want to calculate moving average of val column. I have included manual calculations in last column for your reference
hi Anonymous ,
you may try to add a calculated column like:
Column = AVERAGEX( FILTER( data, data[Site]=EARLIER(data[Site]) &&data[Date]<=EARLIER(data[Date]) ), data[Value] )it worked like:
Moving AVG = AVERAGEX( WINDOW( 1, ABS, 0, REL, DATA, ORDERBY( DATA[Date] ), DEFAULT, PARTITIONBY( DATA[Site] ), MATCHBY( DATA[Site], DATA[Date] ) ), DATA[Value] )
4 Replies
Hey Anonymous
do want that as a new cal column or measure?
btw, check this link and watch Greg's video:
https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Urgent-assistance-Calculate-the-moving-average-of-a-measure/m-p/3061542If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn | Twitter | Blog | YouTube
hi Anonymous ,
you may try to add a calculated column like:
Column = AVERAGEX( FILTER( data, data[Site]=EARLIER(data[Site]) &&data[Date]<=EARLIER(data[Date]) ), data[Value] )it worked like:
Moving AVG = AVERAGEX( WINDOW( 1, ABS, 0, REL, DATA, ORDERBY( DATA[Date] ), DEFAULT, PARTITIONBY( DATA[Site] ), MATCHBY( DATA[Site], DATA[Date] ) ), DATA[Value] )Hi Anonymous ,
Could you please tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please feel free to let me know.
Best regards,
Community Support Team_Binbin YuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
