The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Solved! Go to 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" )
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" )
@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
Create a common Customer table and plot data against it
User | Count |
---|---|
28 | |
12 | |
8 | |
7 | |
5 |
User | Count |
---|---|
36 | |
14 | |
12 | |
7 | |
7 |