Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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...
Need Help
Show and Tell
Tips and Tricks
  • FreemanZ's avatar
    FreemanZ
    2 years ago

    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:

  • ThxAlot's avatar
    ThxAlot
    2 years ago
    Moving AVG = 
AVERAGEX(
    WINDOW(
        1, ABS,
        0, REL,
        DATA,
        ORDERBY( DATA[Date] ),
        DEFAULT,
        PARTITIONBY( DATA[Site] ),
        MATCHBY( DATA[Site], DATA[Date] )
    ),
    DATA[Value]
)

ThxAlot's avatar
ThxAlot
Super User
2 years ago
Moving AVG = 
AVERAGEX(
    WINDOW(
        1, ABS,
        0, REL,
        DATA,
        ORDERBY( DATA[Date] ),
        DEFAULT,
        PARTITIONBY( DATA[Site] ),
        MATCHBY( DATA[Site], DATA[Date] )
    ),
    DATA[Value]
)