Forum Discussion

ptmuldoon's avatar
ptmuldoon
Icon for Resolver I rankResolver I
1 year ago
Solved

Get Average of Last X Values

I'm working in Excel with PowerQuery and PowerPivot, and trying to figure out the correct formula/Measure that will return to the average of the Last X Values.   I've created a measure called 'Diff...
  • SundarRaj's avatar
    1 year ago

    Hi ptmuldoon , is this what you are looking for? I'll attach the images of the Source, Output and M code. Thanks. Let me if I understood your query correctly.

    Here's the code:
    let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Row Labels", type any}, {"Differentials", type number}}),
    Indexing = Table.AddIndexColumn(#"Changed Type","Index",0,1),
    Numbers = Table.TransformColumns(Indexing,{"Index", each {_ - 6.._}}),
    Negative = Table.TransformColumns(Numbers,{"Index", each List.Select(_, each _ >= 0)}),
    Records = Table.TransformColumns(Negative,{"Index", each List.Transform(_, each #"Changed Type"{_})}),
    Average = Table.TransformColumns(Records, {"Index",each if Table.RowCount(Table.FromRecords(_)) = 7 then List.Average(Table.FromRecords(_)[Differentials]) else null}),
    Cols = Table.RenameColumns(Average,{{"Index", "Rolling Average"}})
    in
    Cols

    (I have hardcoded the Rolling Average value that is 7, but that can added as a new step for it).