Forum Discussion
Resolver IGet 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 'Differential', and now I'm trying to get a rolling average of the last 7 values. The values are in date order, but do not exist for every day. Example of the existing pivot table being generated below. Can I create a measure of the rolling 7 values from the 'differential' column/measure?
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).
5 Replies
- lbendlin
Super User
In PowerQuery you would use List.Select and List.Average . In a measure you would use AVERAGEX and WINDOW.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 - SundarRaj
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 PabbuHi @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 PabbuHi @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
