Adding column for Rolling Average (6 month)

[bd27]

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

[v-junyant-msft]

Hi @bd27 ,

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.

[bd27]

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.

[v-junyant-msft · Accepted solution]

Hi @bd27 ,

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.

[lbendlin]

Does it have to be Power Query ? This would be much easier using the DAX WINDOW function.