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.

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

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

  • 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.