Comparing Latest Dates from Two Different Tables and Returning a value of Yes or No

[ntm1275]

Each table has a Supplier in it with possible multiple dates in it and I want to write a measure or add a column that compares the dates and if they match return a value of Yes or No if they don't.

Problem I have is that each supplier has possible multiple dates, so I want to compare the latest dates in Table1 with the latest dates in Table2

So an example of what I have in each table is this using DD/MM/YYYY

Table 1

Customer	Date
Tom	21/08/2021
Tom	13/01/2019
Tom	23/04/2017
Jeff	04/07/2021
Dave	09/10/2021
Dave	17/04/2019
Harry	11/01/2022

Table 2

Customer	Date
Tom	13/04/2021
Tom	13/01/2019
Jeff	04/07/2021
Dave	09/10/2021
Dave	17/04/2019
Harry	26/10/2022

So the result would look like this when I use it to create a table visualisation

Customer	Table1 Date	Table 2 Date	Match
Tom	21/08/2021	13/04/2021	No
Jeff	04/07/2021	04/07/2021	Yes
Dave	09/10/2021	09/10/2021	Yes
Harry	11/01/2022	26/10/2022	No

The reason for trying to do this is becasue both tables are being fed from two seperate databases.

Both databases should match, but with user error, quite a lot of them don't and I'm looking for an easy way to display this in a table visualisation so that both users of each database can view the table visualisation and can clearly see by the 'No', which dates they have entered incorrectly.

Many thanks for your help

[tamerj1 · Accepted solution]

Hi @ntm1275 
Here is a sample file with the solution https://www.dropbox.com/t/7I9C0OMjOQrZMbhu

The simplest method is to have a common filter table that contans all the unique Costomer Names and create a a relationship with the the two tables. The rest is simple

Table1 Max Date = MAX ( Table1[Date] )

Table2 Max Date = MAX ( Table2[Date] )

Match = IF ( [Table1 Max Date] = [Table2 Max Date], "Yes", "No" )

[amitchandak]

@ntm1275 , In both table, you can get latest value using

https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

or

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

Create a common Customer table and plot data against it

https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solutio...