Comparing Dates in Multiple Tables

[AbsoluteNovice]

Hi,

I'm pretty new to Power BI and am completely stumped on this one. I have data in three different tables and I need to find the most recent Meter Reading Date (Table 2) or Overhaul date (Table 3), which ever is greater, for each Asset (Table 1). 

Each asset has a unique identified (serial number) but can have multiple Meter Readings and Overhaul dates. 

Each table has a lot of other columns I haven't included for simplicity but these the key ones.

Table 1 - Assets: 

Serial Number	Asset Status	Asset Location
1234	Operational	Storeroom 1
1235	Operational	Storeroom 2
1236	Operational	Storeroom 3
1237	Operational	Storeroom 3

Table 2 - Meter Readings

Serial Number	Date of Last Reading
1234	1/01/2024
1235	1/02/2024
1236	1/03/2024
1237	1/04/2024
1234	1/01/2022
1235	1/02/2022
1236	1/03/2022
1237	1/04/2022
1234	1/01/2020
1235	1/02/2020
1236	1/03/2020
1237	1/04/2020

Table 3 - Overhauls:

Serial Number	Date of Last Reading
1234	1/04/2024
1235	1/03/2024
1236	1/02/2024
1237	1/01/2024
1234	1/04/2020
1235	1/03/2020
1236	1/02/2020
1237	1/01/2020

Thanks!

[Anonymous]

Your solutions is so great @ryan_mayu 

Hi, @AbsoluteNovice 

Have you solved the current problem? If yes, you can share your solution here or mark the help that is useful to you as a solution so that other members of the community can quickly find the answer when they encounter similar problems. Thank you again.

Best Regards

Jianpeng Li

[ryan_mayu · Accepted solution]

@AbsoluteNovice 

pls see if this is what you want .

create relationships between tables and create a measure

Measure = max(max('Overhauls'[Date of Last Reading]),max('Meter Readings'[Date of Last Reading                     ]))

pls see the attachment below