Forum Discussion

RamblingFire's avatar
RamblingFire
Regular Visitor
1 year ago
Solved

Moving average across columns

Hi, 

 

I have data like:

Name, 2024-01, 2024-02, 2024-03, etc to 2025-01

Customer a, 5,5,10,10 etc

Customer b, 10,11,11,12 etc

Customer c, 0,0,0,0....7,7 as they only have values for last 2 months

 

I'd like a column added that gives moving average over as many months as there is data to indicate a trend

 

Is that possible? 

  • Irwan's avatar
    Irwan
    1 year ago

    hello RamblingFire 

     

    i might be misunderstood but please check if this accomodate your need.

     

    1. i assumed your table looks like this based on your description above.

    2. unpivot the table and rename the header if needed.

    3. create a new measure with following DAX

    Average =
    var _Name = SELECTEDVALUE('Table'[Name])
    Return
    AVERAGEX(
        FILTER(
            ALL('Table'),
            'Table'[Name]=_Name&&
            'Table'[Date]<=MAX('Table'[Date])
        ),
        'Table'[Value]
    )
    4. plot into table visual
     
    Hope this will help.
    Thank you.

1 Reply

  • Irwan's avatar
    Irwan
    Super User
    1 year ago

    hello RamblingFire 

     

    i might be misunderstood but please check if this accomodate your need.

     

    1. i assumed your table looks like this based on your description above.

    2. unpivot the table and rename the header if needed.

    3. create a new measure with following DAX

    Average =
    var _Name = SELECTEDVALUE('Table'[Name])
    Return
    AVERAGEX(
        FILTER(
            ALL('Table'),
            'Table'[Name]=_Name&&
            'Table'[Date]<=MAX('Table'[Date])
        ),
        'Table'[Value]
    )
    4. plot into table visual
     
    Hope this will help.
    Thank you.