Forum Discussion
Solved
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...
see my video
https://1drv.ms/v/s!AiUZ0Ws7G26Rhz_GpGudZ9ZPoqtg?e=dsWbNL
Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26Rhz6qlA7kcAK3woHZ?e=XGQ2cR
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.
