Excel Forum

Rolling Total and Rolling Max

Mark Smith

Member

Members

Forum Posts: 9

Member Since:
February 19, 2021

Offline

February 19, 2021 - 12:00 pm

Following on from the very good YouTube post Power Query Running Totals = The Right Way! I have a follow up question that is closely related to that. In the attached test example in the raw data sheet we have fictional PNL data for weekdays over a qtr, the raw PNL data is the daily PNL (not cumulative).

From this input I need Power Query (in Excel) to add two columns which are manually added on the Expected Output sheet. First Column is the cumulative PNL which I can add following the YouTube video (thanks v much). The second column is proving harder. From the PNL I need to find the MAX cumulative PNL over a rolling 5 day period. You will see the result in Expected Output. So on Day 5 it looks for the Max Cumulative PNL of the previous 5 days (days 1-5), on Day 6 it looks for the Max Cumulative PNL of the previous 5 days (days 2-6) so that needs to roll forward 1 day.

I am sure this is very achievable, but it is beyond my expertise today. Any ideas would be most welcome. Thanks

(I have my current Power Query results included in the attached. I can't get it to roll. It basically always shows me only the Max of the first 5 days).

See Answer

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

February 22, 2021 - 2:49 pm

Hi Mark,

Use this adjusted query:

let
Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Portfolio", type text}, {"PNL", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Sum(List.FirstN(#"Added Index"[PNL],[Index]))),
Tbl = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
PNLList = List.Buffer(Tbl[Custom]),
#"Added Custom2" = Table.AddColumn(Tbl, "Custom.1", each List.Max(List.Range(PNLList,[Index]-5,5)))
in
#"Added Custom2"