Get value from another table if there is a difference between two dates

[Anonymous]

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]

@Fowmy , it worked for me. thanks for your help. Much appreciated

[Fowmy · Accepted solution]

@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
)

[Anonymous]

Hi @amitchandak,
Can you please help?

[Ashish_Mathur]

For ID3, why should the answer be Mobile?

[Anonymous]

Because initally when the inspection was performed, mobile was used

[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?

[Anonymous]

Yes, That's Right

[Ashish_Mathur]

For ID3, there are two different Appliances on 20/02/2020.  Why should the answer be Mobile (and not Tablet)?

[Anonymous]

@Ashish_Mathur Because, initially mobile was used to record the transaction

[Ashish_Mathur]

Hi,

Please show the expected result.

[Anonymous]

@Ashish_Mathur Thanks for your reply, Please have a look to the post.