Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Adding column for Rolling Average (6 month)

I need help to add a new column to my table that will show the 6 month rolling average. Date column to use is M-Y and want to get 6 month rolling average of the Order Value    
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    Sorry for being late!
    You can try this DAX:

    _average = 
CALCULATE(
    AVERAGE('Table'[Order Value]),
    ALLEXCEPT('Table', 'Table'[Sold To Region]),
    DATESINPERIOD(
        'Table'[M-Y],
        LASTDATE('Table'[M-Y]),
        -6,
        MONTH
    )
)

    And the final output is as below:


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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous ,

Just as lbendlin says, DAX will be much more easier.
Here is my sample data:



1. Power Query solution:
Just put all of these M funxtions into the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdJBDoQgDAXQu7DWRApUXKrHMN7/GkP/QIZ8MZkFZuIbfkvxupwsIrMXN7m9rKTl4d099XCUJWkAZ1kx9xDmxVdIan8REsvyaQBWPbwBivTw69d+BRIUWV5AUL6XUDtWHCqSoLERnG1eDEfr+O8dVkMCQaxR2JEILArtMqBdep/qe7V5KsHZboQBw1IC7c+3DgBRDHuLYrDieRSFT2u0Y8fBA8naBMPKneS+r43AqgQl2GrWFr+f0VMwx4fgdoeCW2xp9wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"M-Y" = _t, #"Sold To Region" = _t, #"Order Value" = _t, #"Date Index" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"M-Y", type date}, {"Sold To Region", type text}, {"Order Value", Int64.Type}, {"Date Index", Int64.Type}}),
    RollingAverage = Table.AddColumn(#"Changed Type", "Rolling Avg 6 Month", each List.Average(List.Transform(Table.SelectRows(#"Changed Type", (row) => row[#"M-Y"] <= [#"M-Y"] and row[#"M-Y"] > Date.AddMonths([#"M-Y"], -6))[Order Value], each _)))
in
    RollingAverage

The final output is as below:


2. DAX solution:
Use this DAX to create a calculated column:

_average = 
CALCULATE(
    AVERAGE('Table'[Order Value]),
    ALL('Table'),
    DATESINPERIOD(
        'Table'[M-Y],
        LASTDATE('Table'[M-Y]),
        -6,
        MONTH
    )
)

The final output is as below:


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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi v-junyant-msft, thank you for your response. What do I need to add to DAX to separate it by Sold To Region? Region A should have a different value than B, etc.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    Sorry for being late!
    You can try this DAX:

    _average = 
CALCULATE(
    AVERAGE('Table'[Order Value]),
    ALLEXCEPT('Table', 'Table'[Sold To Region]),
    DATESINPERIOD(
        'Table'[M-Y],
        LASTDATE('Table'[M-Y]),
        -6,
        MONTH
    )
)

    And the final output is as below:


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