Forum Discussion
Get value from another table if there is a difference between two dates
Hello Folks,
I have 2 given tables. Now I want to create a new column in table as New_Appliance where if there is a date difference between two dates in table 1 then look for value from table 2 where we can get the appliance name from Re-inspection date which is the lowest. For example: For Id 2, the new_appliance value should be Device.
Table1
Id | Inspection Date | Re-inspection Date | Appliance |
1 | 01/01/2020 | 01/01/2020 | Mobile |
2 | 15/02/2020 | 17/02/2020 | Tablet |
3 | 20/02/2020 | 23/02/2020 | Tablet |
4 | 25/02/2020 | 25/02/2020 | Device |
5 | 27/02/2020 | 01/03/2020 | Device |
Table 2
Id | Inspection Date | Re-inspection Date | Appliance |
1 | 01/01/2020 | 01/01/2020 | Mobile |
2 | 15/02/2020 | 15/07/2020 | Device |
2 | 15/02/2020 | 16/02/2020 | Mobile |
2 | 15/02/2020 | 17/02/2020 | Device |
3 | 20/02/2020 | 20/02/2020 | Mobile |
3 | 20/02/2020 | 21/02/2020 | Tablet |
3 | 20/02/2020 | 23/02/2020 | Device |
4 | 25/02/2020 | 25/02/2020 | Device |
5 | 27/02/2020 | 27/02/2020 | Device |
5 | 27/02/2020 | 28/02/2020 | Mobile |
5 | 27/02/2020 | 01/03/2020 | Device |
Expected Table
What I need is if there is a date difference in inspection & re-inspection date in table 1, I want to know what device was used initially to record the transaction:
This is how the new calculated column look like:
Id | Inspection Date | Re-inspection Date | Appliance | New_Appliance |
1 | 01/01/2020 | 01/01/2020 | Mobile | Mobile |
2 | 15/02/2020 | 17/02/2020 | Tablet | Device |
3 | 20/02/2020 | 23/02/2020 | Tablet | Mobile |
4 | 25/02/2020 | 25/02/2020 | Device | Device |
5 | 27/02/2020 | 01/03/2020 | Device | Device |
Anonymous
Please add the following column to your Table 1. Your results are not in line with the explanation that you provided. ID 2, but the lowest should be "Mobile" as the lowest date is 16/2/2020.New Appliance = var __id = 'Table-1'[Id] return IF( 'Table-1'[Inspection Date] = 'Table-1'[Re-inspection Date] , 'Table-1'[Appliance], var __mindate = MINX( FILTER( 'Table-2', 'Table-2'[Id] = __id ), 'Table-2'[Re-inspection Date] ) var __device = MAXX( FILTER( 'Table-2', 'Table-2'[Id] = __id && 'Table-2'[Re-inspection Date] = __mindate ), 'Table-2'[Appliance] ) return __device )
11 Replies
- Fowmy
Super User
Anonymous
Please add the following column to your Table 1. Your results are not in line with the explanation that you provided. ID 2, but the lowest should be "Mobile" as the lowest date is 16/2/2020.New Appliance = var __id = 'Table-1'[Id] return IF( 'Table-1'[Inspection Date] = 'Table-1'[Re-inspection Date] , 'Table-1'[Appliance], var __mindate = MINX( FILTER( 'Table-2', 'Table-2'[Id] = __id ), 'Table-2'[Re-inspection Date] ) var __device = MAXX( FILTER( 'Table-2', 'Table-2'[Id] = __id && 'Table-2'[Re-inspection Date] = __mindate ), 'Table-2'[Appliance] ) return __device ) - Ashish_Mathur
Hi,
Please show the expected result.
Ashish_Mathur Thanks for your reply, Please have a look to the post.
- Ashish_Mathur
For ID3, why should the answer be Mobile?
- Ashish_Mathur
Hi,
So if there is difference in the 2 dates of any ID in Table1, then you want to search for the Inspection date appearing in Table1 in the Inspection Date of Table2 and then bring over the Device from Table2 to Table1. is my understanding correct?
Hi amitchandak,
Can you please help?Fowmy , it worked for me. thanks for your help. Much appreciated
