Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Making a moving average backwards

Hello everyone! I Have the follow problem, I want to do a moving average backwards. for example, as the table below: Date Value 01/01/2019 10 02/01/2019 11 03/01/2019 14 04/01/2...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous 

    You may create a measure like below:

    Measure =
DIVIDE (
    CALCULATE (
        SUM ( Table2[Value] ),
        FILTER ( ALL ( Table2 ), Table2[Date] >= MAX ( Table2[date] ) )
    ),
    COUNTROWS ( FILTER ( ALL ( Table2 ), Table2[Date] >= MAX ( Table2[date] ) ) )
)

    Regards,

    Cherie

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
7 years ago
let
    Source = Web.Page(Web.Contents("https://community.powerbi.com/t5/Desktop/Making-a-moving-average-backwards/m-p/649510#M311441")),
    Data0 = Source{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Value", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
    Custom1 = List.Skip(List.Accumulate(#"Added Index"[Value],{0},(state,current)=> state&{List.Last(state)+current})),
    Custom2 = Table.FromColumns(Table.ToColumns(#"Added Index")&{Custom1}),
    #"Inserted Division" = Table.AddColumn(Custom2, "Moving Average Backwards", each [Column4] / [Column3], type number),
    #"Sorted Rows1" = Table.Sort(#"Inserted Division",{{"Column3", Order.Descending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Column3", "Column4"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Date"}, {"Column2", "Value"}})
in
    #"Renamed Columns"