Most recent two records with multiple rows

[timbo1966]

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.

[danextian]

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.
 

[timbo1966]

Thank you for taking the time to look at this. I am sure this will work, but the solution from @Ahmedx was easier for me to understand with my relatively basic PowerBI skills.

[Ahmedx · Accepted solution]

see my video

https://1drv.ms/v/s!AiUZ0Ws7G26Rhz_GpGudZ9ZPoqtg?e=dsWbNL
Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26Rhz6qlA7kcAK3woHZ?e=XGQ2cR

[timbo1966]

That is amazing! Does exactly what I need. Thank you so much for taking the time.

[Ashish_Mathur]

Hi,

Show the expected result very clearly.

[timbo1966]

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.

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Building", type text}, {"DateEnd", type date}, {"ScheduleNo", Int64.Type}, {"Total", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Building"}, {{"Grouped", each _, type table [Building=nullable text, DateEnd=nullable date, ScheduleNo=nullable number, Total=nullable number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.AddRankColumn([Grouped],"Rank",{"DateEnd",Order.Descending},[RankKind = RankKind.Dense])),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"DateEnd", "ScheduleNo", "Total", "Rank"}, {"DateEnd", "ScheduleNo", "Total", "Rank"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom.1", each [Rank] <= 2),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Grouped", "Rank"})
in
    #"Removed Columns"

Hope this helps.

[ThxAlot]

TOP2.pbix