Get Average of Last X Values

[ptmuldoon]

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?

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[SundarRaj · Accepted solution]

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).

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...