Calculate Moving Average

[Anonymous]

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

[FreemanZ · Accepted solution]

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:

[Anonymous]

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 Yu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ThxAlot · Accepted solution]

Moving AVG = 
AVERAGEX(
    WINDOW(
        1, ABS,
        0, REL,
        DATA,
        ORDERBY( DATA[Date] ),
        DEFAULT,
        PARTITIONBY( DATA[Site] ),
        MATCHBY( DATA[Site], DATA[Date] )
    ),
    DATA[Value]
)

[FreemanZ · Accepted solution]

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:

[VahidDM]

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/3061542

If 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