Forum Discussion

kmclorg's avatar
kmclorg
Helper I
9 years ago
Solved

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

I have two tables:

1) Product Purchase Table

CustomerProductPurchase Date
18370A2016-05-02
18370A2016-04-12
18370C2016-03-10
16129A2016-08-11
15691B2016-09-10

 

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

CustomerProductContact DateResult I Want To Find:
A purchase of this product was made within one month after contact
18370C2016-04-01FALSE
18370B2016-04-01FALSE
18370A2016-04-01TRUE
16129A2016-07-06FALSE
15691B2016-08-18TRUE

 

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!

  • kmclorg's avatar
    kmclorg
    9 years ago

    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

     

4 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    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's avatar
      kmclorg
      Helper I
      9 years ago

      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's avatar
        Vvelarde
        Community Champion
        9 years ago

        kmclorg

         

        create a table with customers and related to both tables.

         

        You can do it this in Modeling Menu, New Table

         

        =Distinct(Table2[Customer])