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:

DateValue
01/01/201910
02/01/201911
03/01/201914
04/01/201915
05/01/201918
06/01/201919
07/01/201918
08/01/201920
09/01/201914
10/01/201925


So what I want to show is: On date 10/01/2019, the value that must show is 25. 
On day 09/01/2019, it must show the average between day 10 and 9, given me a total of 19,5  (That is (20+14)/2)
On day 08/01, it must show an average of the days 10, 9 and 8, given me a total of 19,6 (That is (20+14+25)/3)
On day 07/01 must show the average of the day 10,9,8,7 and so on....

How can I do this?

Thank you!

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

2 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    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
    Community 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"