Forum Discussion
Most recent two records with multiple rows
Hi,
I am struggling with a problem regarding the analysis of property cost data. I want to be able to compare the most recent two budgets that I have for a number of buildings. The key variables are:
- Buildings have differing year-end dates (for the cost year), e.g., 31-12-2023, 31-03-2024
- I don't always have up to date records to compare. So, for one building it could be 31-12-2023 with 31-12-2022 and for another 31-03-2022 with 31-03-2021.
- There will be differing numbers of records, so I might have two years worth of data for one building and five for another
- Costs are split into separate schedules, so I could have just one schedule for one building and three for another. The schedules have to be kept separate, as an occupier will pay a different percentages for each.
I have included a small simplified table of sample data that shows what I am trying to analyse:
| Building | DateEnd | ScheduleNo | Total | Management | Security | Cleaning | Repairs |
| A | 31/12/2023 | 1 | 700000 | 70000 | 280000 | 210000 | 140000 |
| A | 31/12/2023 | 2 | 300000 | 30000 | 120000 | 90000 | 60000 |
| A | 31/12/2023 | 3 | 100000 | 10000 | 40000 | 30000 | 20000 |
| A | 31/12/2022 | 1 | 620000 | 62000 | 248000 | 186000 | 124000 |
| A | 31/12/2022 | 2 | 250000 | 25000 | 100000 | 75000 | 50000 |
| A | 31/12/2022 | 3 | 80000 | 8000 | 32000 | 24000 | 16000 |
| B | 31/03/2024 | 1 | 500000 | 50000 | 200000 | 150000 | 100000 |
| B | 31/03/2024 | 2 | 150000 | 15000 | 60000 | 45000 | 30000 |
| B | 31/03/2023 | 1 | 450000 | 45000 | 180000 | 135000 | 90000 |
| B | 31/03/2023 | 2 | 125000 | 12500 | 50000 | 37500 | 25000 |
I have managed to extract all of the rows with the most recent date using the List.Max function, but I am struggling to extract the second most recent date. I have tried List.MaxN, but as there are multiple rows for some of the buildings, due to the schedules, this doesn't work.
I have been thinking that if I could somehow earmark and exclude the most recent dated records for each building, then I would be left with the next most recent, but I have been able to figure out how to implement this.
Any ideas would be greatly appreciated.
see my video
https://1drv.ms/v/s!AiUZ0Ws7G26Rhz_GpGudZ9ZPoqtg?e=dsWbNL
Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26Rhz6qlA7kcAK3woHZ?e=XGQ2cR
8 Replies
Hi,
Show the expected result very clearly.
I'll simplify the table, but I probably need a few more rows to show what I mean:
Building DateEnd ScheduleNo Total A 31/12/2023 1 700000 A 31/12/2023 2 300000 A 31/12/2023 3 100000 A 31/12/2022 1 62000 A 31/12/2022 2 250000 A 31/12/2022 3 80000 A 31/12/2021 1 580000 A 31/12/2021 2 220000 A 31/12/2021 3 70000 B 31/03/2024 1 500000 B 31/03/2023 1 450000 B 31/03/2022 1 400000 Afterward I would want to end up with the following:
Building DateEnd ScheduleNo Total A 31/12/2023 1 700000 A 31/12/2023 2 300000 A 31/12/2023 3 100000 A 31/12/2022 1 62000 A 31/12/2022 2 250000 A 31/12/2022 3 80000 B 31/03/2024 1 500000 B 31/03/2023 1 450000 So, the 2021 dated rows for Building A and the 2022 dated row for Building B are excluded.
see my video
https://1drv.ms/v/s!AiUZ0Ws7G26Rhz_GpGudZ9ZPoqtg?e=dsWbNL
Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26Rhz6qlA7kcAK3woHZ?e=XGQ2cRThat is amazing! Does exactly what I need. Thank you so much for taking the time.
Hi timbo1966 ,
Please try the code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdDNDcAgCAbQXTiblB9te23XMO6/RoXKDSThgC98CfYODxQQOogPRpY50OwLtWCUwFkfNm4ZifPKPzll65ava/wdM630tnGL59zFrzd+f0ZRrh6Psfvn1Ra7H1/X/vgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Building = _t, DateEnd = _t, ScheduleNo = _t, Total = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Building", type text}, {"DateEnd", type date}, {"ScheduleNo", Int64.Type}, {"Total", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Building", "ScheduleNo"}, {{"Grouped", each _, type table [Building=nullable text, DateEnd=nullable date, ScheduleNo=nullable number, Total=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let sorted = Table.Sort([Grouped],{{"DateEnd", Order.Descending}}) in Table.AddIndexColumn(sorted,"Index",1,1)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"DateEnd", "Total", "Index"}, {"DateEnd", "Total", "Index"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Index] <> 3)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Grouped"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"DateEnd", type date}, {"Total", type number}}) in #"Changed Type1"Warning: This can be very slow on a large table. I personally prefer using DAX for this kind of scenario as it is more optimized at scanning a table than PQ.
