cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

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

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

 

View solution in original post

4 REPLIES 4
Highlighted
Community Champion
Community Champion

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

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

 




Lima - Peru
Highlighted
Helper I
Helper I

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

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

 

Highlighted
Community Champion
Community Champion

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

@kmclorg

 

create a table with customers and related to both tables.

 

You can do it this in Modeling Menu, New Table

 

=Distinct(Table2[Customer])

 

 




Lima - Peru
Highlighted
Helper I
Helper I

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

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

 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors