How to compare dates of two different columns in two different tables?

[Black_magic100]

I have two tables: Bookings and Users.

They are related through CustuserID from the Bookings table and UserID from the Users table.

I would like to create a boolean column (true or false) to for whether or not a row from the Users table is BEFORE a row in the Bookings table.  If it is then True and vice versa.

The fields I am comparing are Users.MemberSince and Bookings.BookingDate.  If the User is indeed a member and they became a member BEFORE the booking date I need to filter them out as those are customers who have already converted.

[Ashish_Mathur]

Hi,

Share some data (of both Tables) that can be pasted by me in an Excel file.

[Black_magic100]

Okay I will just keep it simple...

Users:

FirstName: Denis, Amanda, Paige

UserID: 1, 2, 3

MemberSinceDate: Thursday, December 21, 2017, Null, Null

MemberSinceTIme: 12:00:00 AM

Bookings:

CustUserID: 1, 2, 3

BookingDate: Thursday, January 10, 2019

BookingTime: 12:00:00 AM

If done right, only Amanda and Paige should show.  The MemberSinceDate and MemberSinceTime need to be BEFORE (less than) the BookingDate and BookingTime respectively.  Please keep in mind that the users I would like to show will not always be Null.  It is possible to have a MemberSince date after the BookingDate as those are the people who converted right after their session and thus I would like to track them.

[v-juanli-msft · Accepted solution]

Hi @Black_magic100

Create measures

Measure =
IF (
    MAX ( Bookings[BookingDate] ) = BLANK (),
    "null",
    CALCULATE ( MAX ( Bookings[BookingDate] ), ALLEXCEPT ( Users, Users[UserID] ) )
)

Measure 2 =
IF (
    MAX ( Users[MemberSinceDate] ) = BLANK ()
        || MAX ( Users[MemberSinceDate] ) >= [Measure],
    0,
    1
)

Add [Measure 2] in the visual level filter.

Best Regards
Maggie

“
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
“