Lookup other Table based on Product and Date range

[Anonymous]

Hey!

Current data example, working in the insurance business:

Table 1: Insurances gained

Customer_ID |  InsuranceNumber(unique) |  Product  |    Date        | Indicator_Moved_policy

1                             10101                               House       202001           1

2                             10102                               House       202001           0

1                             10110                                Car           202007           1

Table 2: Insurances lost

Customer_ID |  InsuranceNumber(unique) |  Product  |    Date          | Indicator_Moved_policy

1                             10112                               House       202003           1

1                             10130                                Car           202001           0

2                             10140                                Car           202010           1

What we're seeing here is that customer 1 has had a time with 2 house insurances, before stopping 1 of them. This could be due to customer 1 moving places. I need to have an indicator in (preferably)  both tables if a customer has had a similar product (so not the exact same insurancenumber) in the past 3 months. Highlighted in bold is what I want to achieve.

For example: 

In the Insurance LOST table I have to check if, when a certain product was lost (customer 1: house), if this same customer has GAINED the same product (not the same insurancenumber), in the last 3 months. If so, mark this customer with 1. This way I can filter them.

I'd love to hear what the possibilties are. If you need any more info, i'd love to provide them :)!

Kind regards,

Daniël

[v-alq-msft]

Hi, @Anonymous 

You may try the following calculated column in 'table2' to see if it works.

Indicator_Moved_policy =
var cid = table2[Customer_ID]
var d = table2[Date]
var p = table2[Product]
var i = table2[insuranceNumber]
return
IF (
    COUNTX (
        FILTER (
            Table1,
            table1[Customer_ID] = cid
                && table1[Product] = p
                && d >= table1[Date] - 90
                && d <= table1[Date] + 90
                && i<>table1[insuranceNumber]
        ),
        Table1[Customer_ID]
    ) + 0 > 0,
    1,
    0
)

Best Regards

Allan

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[amitchandak · Accepted solution]

@Anonymous , try like

Assume you have a date
New column in Table 1

Indicator_Moved_policy= if(countx(filter(Table2, table1[Customer_ID] =table2[Customer_ID] && table1[Product] =table2[Product] && table1[Date] >=table2[Date] -90 && table1[Date] <=table2[Date] +90), Table2[Customer_ID])+0>0,1,0)

New column in Table 2

Indicator_Moved_policy= if(countx(filter(Table1, table1[Customer_ID] =table2[Customer_ID] && table1[Product] =table2[Product] && table2[Date] >=table1[Date] -90 && table2[Date] <=table1[Date] +90), Table1[Customer_ID])+0>0,1,0)

[Anonymous]

Cool! That seems to work. Is there a way to have a build in check, to make sure it isn't the same policy number?

Can you add like a && Table1[InsuranceNumber] <> Table2[InsuranceNumber] && in between?