Forum Discussion
Get Average of Last X Values
- 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).
Hi ptmuldoon,
Thanks SundarRaj and lbendlin for Addressing the issue.
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.
Regards,
Vinay Pabbu
Hi @ptmuldoon,
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.
Regards,
Vinay Pabbu
- Anonymous1 year agoNot applicable
Hi @ptmuldoon,
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Regards,
Vinay Pabbu