Help with Join with Date-Specific Contingencies

[apmulhearn]

Hello!

I have two tables.

I need to join them for attribution purposees.

These are the criteria I need to be true:

1. Inquiries are joined on Client ID between tables

2. Inquiries are associated with closest First_Payment_Date which is NOT AFTER that Date

Table 1: Inquiries
Created_On	Subject	Medium	Campaign	Client_ID
1/1/2015	Africa	Hubspot	Email	12345
3/1/2015	India	Facebook	Discount	12345
4/1/2015	Kenya	Instagram	Giraffe	12345
7/1/2017	Tanzania	Facebook	Zebra	12345
8/1/2017	Botswana	Email	Thanksgiving	12345

Table 2: Guests
Client_ID	Booking_Name	First_Payment_Date
12345	Clients_First_Booking	6/1/2015
12345	Clients_Second_Booking	10/1/2017

Goal Result Table
Created_On	Subject	Medium	Campaign	Client_ID	Booking_Name	First_Payment_Date
1/1/2015	Africa	Hubspot	Email	12345	Clients_First_Booking	6/1/2015
3/1/2015	India	Facebook	Discount	12345	Clients_First_Booking	6/1/2015
4/1/2015	Kenya	Instagram	Giraffe	12345	Clients_First_Booking	6/1/2015
7/1/2017	Tanzania	Facebook	Zebra	12345	Clients_Second_Booking	10/1/2017
8/1/2017	Botswana	Email	Thanksgiving	12345	Clients_Second_Booking	10/1/2017

Many thanks for any help in doing this!

[apmulhearn]

@ryan_mayu - you gave me exactly what I asked, thank you, and I marked it as a solution.

I realize though that I need to take it a step more. I believe I need a measure but I'm a little stuck as to what it is.

The image below is using Inquires[Booking_Name} and Charting it under other Inquiries fields.  

The three lines with the blue dots are the same Inquiries[Booking_Name] line because it is associated with 3 different Inquiries. 

I would like it only to highlight the one with the earliest Inquiries [Created Date] (not used in the graphic). Can you help me here?

[ryan_mayu]

@apmulhearn 

could you pls share the pbix file after removing the sensitive data?

[apmulhearn]

It is loaded with sensitive data and will take me a bit of time to remove, but you are helping me, so I will gladly do it!

In the meantime, though, I think I can exemplify it based on the small sample you helped me with above. If this doesn't help I will circle back asap.

[ryan_mayu]

@apmulhearn 

why not use the opposite way to search data? searching from Inquiries table instead of searching from guest table.

pls see the attachment below

[apmulhearn]

Hi @ryan_mayu 

Thank you for the suggestion to you Guest instead of Inquiry. Unfortunately, there are (potentially) multiple items on each of those tables. That is, there are multiple Guests assoiciated with a booking, and multiple Inquiries associated with a booking.  

I substituted "Campaign" for "Medium" to achieve the second level of drill-down data I need (Medium for me is Marketing Platform, Campaign is Marketing Item.). In doing so, since there were multiple guests on George's 2022 booking, he gets lumped in with two different Marketing Campaigns - he and his wife each clicked one. I want to know which was clicked first and attribute ONLY there.

[ryan_mayu]

@apmulhearn 

I think I still need you to provide me some dummy data and the expected output.

[ryan_mayu · Accepted solution]

@apmulhearn 

you can use DAX to create two columns

Booking_name = 
var _date=MINX(FILTER(Guests,Guests[Client_ID]=Inquiries[Client_ID]&&Guests[First_Payment_Date]>Inquiries[Created_On]),Guests[First_Payment_Date])
return maxx(FILTER(Guests,Guests[Client_ID]=Inquiries[Client_ID]&&Guests[First_Payment_Date]=_date),Guests[Booking_Name])

First_payment_Date = 
var _date=MINX(FILTER(Guests,Guests[Client_ID]=Inquiries[Client_ID]&&Guests[First_Payment_Date]>Inquiries[Created_On]),Guests[First_Payment_Date])
return maxx(FILTER(Guests,Guests[Client_ID]=Inquiries[Client_ID]&&Guests[First_Payment_Date]=_date),Guests[First_Payment_Date])