Create Boolean column by comparing two tables for hits within a date range

[kmclorg]

I have two tables:

1) Product Purchase Table

Customer	Product	Purchase Date
18370	A	2016-05-02
18370	A	2016-04-12
18370	C	2016-03-10
16129	A	2016-08-11
15691	B	2016-09-10

2)  Customer Contact Table (the date we met with the customer to recommend products)

Customer	Product	Contact Date	Result I Want To Find: A purchase of this product was made within one month after contact
18370	C	2016-04-01	FALSE
18370	B	2016-04-01	FALSE
18370	A	2016-04-01	TRUE
16129	A	2016-07-06	FALSE
15691	B	2016-08-18	TRUE

I want to add a boolean column to the Customer Contact Table that will show if any purchases for the product and customer occurred less than ONE MONTH after the Customer Contact Date.  (any number of purchases might have been made before, during or after that time frame)

I put in a sample of what the boolean column SHOULD calculate to, but I cannot figure out how to write a measure for this in DAX or create a column for it in Power Query. 

Any suggestions?

THANKS!

[Vvelarde]

@kmclorg

hi, try with this measure:

flag =
VAR contactdate =
    MIN ( Table2[Contact Date] )
VAR purchasedate =
    MIN ( Table1[Purchase Date] )
RETURN
    IF (
        purchasedate > contactdate,
        IF ( DATEDIFF ( contactdate, purchasedate, DAY ) > 30, FALSE (), TRUE () )
    )

[kmclorg]

Thanks - this looks good - but I can't make it work for me as there are multiple purchases before and after the contact date.  Since both tables have multiple records with the same client number and product type, I cannot build a relationship between the tables (or maybe I can but I don't know how to make a many to many)

For example If the first Purchase (min purchase date) was in 2015 and the first contact date (min contact date) was in late Feb 2016, and there was another purchase in early March 2016, then the answer should be TRUE but I using this calculation I get FALSE.

Is there something I am doing wrong? Perhaps, instead of a measure, I should be making a column - but I still don't know how I would do that.

Here is a link to the testfile that I created in PowerBI which shows the results of the flag formula https://www.dropbox.com/s/r8fqn2trb7ndzvf/TestCase.pbix?dl=0

[Vvelarde]

@kmclorg

create a table with customers and related to both tables.

You can do it this in Modeling Menu, New Table

=Distinct(Table2[Customer])

[kmclorg · Accepted solution]

Thank you. I will try that.

In the meantime, I have figured out a formula that seems to work if I add a column to my contacts table. It seems pretty long and complicated but it is giving the results I expected. The MERGED column in both files is a column I added that combines Customer ID with Product ID (so I guess this is the equivalent of the customer dimension table you suggested)

Contact Influenced Purchase = 
If(
    CALCULATE(
            COUNTROWS ( Purchases ),
            Filter (
                Filter( 
                    FILTER ( 
                        ALL ( Purchases ), Contacts[Merged]=Purchases[Merged]), 
                (Purchases[Purchase Date]-Contacts[Contact Date]>= 0)),
            (Purchases[Purchase Date]-Contacts[Contact Date]<=31)))>0,
    True,
    False)

  Thanks so much for your help