Hi
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).
Hi Mark,
Use this adjusted query:
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"
