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?

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

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