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...
Need Help
  • SundarRaj's avatar
    SundarRaj
    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).

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago

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