Find a customers next booking

[LaurenceSD]

Hi,

I've got something I'm struggling with, what I'm wanting to find out, for each record is where a guest booked to go next, so we can make some predictions, based on where the majority of guests book, i.e. for all the guests who've booked to go to France, 30% then make their next booking to Spain, etc

I have data in a table with a unique ID for each guest, the date of their booking, and the country they went to & then I've added the Expected Outcome. So Guest 1 first booked to France and their next booking was to the USA

Customer ID	Book Date	Country		Expected Outcome
1	01/01/2000	France		USA
2	01/02/2000	Germany		Portugal
3	01/03/2000	Spain		Null
4	01/04/2000	Italy		Greece
1	01/05/2000	USA		Null
2	01/06/2000	Portugal		Null
5	01/07/2000	Italy		Null
4	01/08/2000	Greece		Null

Any suggestions on how to best achieve this would be gratefully received, the file is already large so hoping for an efficient Dax formula to do the heavy lifting as opposed to a calculated column, which the file is struggling to implement.

Thanks

Laurence

[v-luwang-msft · Accepted solution]

Hi @LaurenceSD  @Kitty-SD ,

According the data you provided,test to create the below column:

rank = RANKX(FILTER(data,data[ContactId]=EARLIER(data[ContactId])),format(data[BookDate],"YYYYMMDD")+data[BookId],,ASC,Dense)

return = CALCULATE(MAX(data[MetaCountry]),FILTER(ALL(data),data[ContactId]=EARLIER(data[ContactId])&&data[rank]=EARLIER(data[rank])+1))

Output result:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[Kitty-SD]

@Jihwan_Kim 

I've used a combination of the book date & book id to create a unique id. I'm still getting the same error. Here is the dataset I've used:
https://drive.google.com/file/d/1lU6o71d4b4lSYLta5HVBM45MtRBnVtAF/view?usp=sharing 

Many thanks in advance,

Kitty

[rohit_singh]

Hi @LaurenceSD ,

Please try this measure in DAX

Next Country =

var _maxdate =

CALCULATE(

MAX(TravelList[Book Date]),

ALLEXCEPT(TravelList, TravelList[Customer ID])

)

var _country =

CALCULATE(

MAX(TravelList[Country]),

FILTER(AllEXCEPT(TravelList,TravelList[Customer ID]),

TravelList[Book Date] = _maxdate)

)

Return

if(SELECTEDVALUE(TravelList[Country]) = _country, "null", _country)

This gives us the desired result

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[Kitty-SD]

Hi,

I made an addition to the dataset in your original file which has resulted in the same error. I think the issue is that a guest could technically make 2 bookings with the same Book Date.

Unfortunately, I seem to be unable to upload a .pbix file on this forum, so here is the entry and hopefully you can replicate the error:
(Customer ID: 1 / Book Date: 05/01/2000 / Country: Tunisia)

Thank you in advance,

Kitty

[Jihwan_Kim]

Hi,

Thank you for the clarification.

May I ask,

if the customerID: 1 booked two countries (USA, Tunisia) on the same date (5th Jan. 2000), which country do you want to show? And what is the logic behind the selection of the country if there are two or more than two countries for the same customerID on the same booking date? Once I can understand the logic, I can try to come up with fixed solution. I purposely created those to show error if there are more than two countries, but sorry that I did not mentioned.

For your information, one of the ways to show error message if there are two or more countries is that, create one-column-one-row TABLE by using VALUES function. And then, if the outcome is not one-row table, the measure shows error.

[Kitty-SD]

Hi,

Thank you for this! So there is a BookId column which would be the decider for when the book dates are the same. I've had a go at resolving this today, but still no success. Here is an example:

So the logic needs to be that for ContactId 616428, since there are duplicate Book Dates, the BookId 4619737 should come before 4620136. Hopefully that helps. Thank you in advance.

[Jihwan_Kim]

Hi,

Please check the below attached pbix file.

I created one for creating a measure and another for creating a calculated column.

Next booking country measure: = 
VAR currentid =
    MAX ( Data[Customer ID] )
VAR currentdate =
    MAX ( Data[Book Date] )
VAR nextbookdate =
    CALCULATE (
        MIN ( Data[Book Date] ),
        FILTER (
            ALL ( Data ),
            Data[Customer ID] = currentid
                && Data[Book Date] > currentdate
        )
    )
VAR nextbookcountry =
    CALCULATETABLE (
        VALUES ( Data[Country] ),
        FILTER (
            ALL ( Data ),
            Data[Customer ID] = currentid
                && Data[Book Date] = nextbookdate
        )
    )
RETURN
    IF (
        HASONEVALUE ( Data[Customer ID] ),
        IF ( ISBLANK ( nextbookcountry ), "Null", nextbookcountry )
    )

Next Booking Country CC = 
VAR nextbookingdate =
    MINX (
        FILTER (
            Data,
            Data[Customer ID] = EARLIER ( Data[Customer ID] )
                && Data[Book Date] > EARLIER ( Data[Book Date] )
        ),
        Data[Book Date]
    )
VAR nextcountry =
    SUMMARIZE (
        FILTER (
            Data,
            Data[Customer ID] = EARLIER ( Data[Customer ID] )
                && Data[Book Date] = nextbookingdate
        ),
        Data[Country]
    )
RETURN
    IF ( ISBLANK ( nextcountry ), "Null", nextcountry )

[Kitty-SD]

Hi @Jihwan_Kim ,

Thank you for looking into this for us! I've tried using the measure you suggested, but I'm getting the following error:

If you have any ideas as to why this is happening, that would be very helpful.

Thanks,

Kitty

[Jihwan_Kim]

Hi,

Thank you for your reply.

Please share your sample pbix file's link, and then I can try to look into it to come up with a more accurate solution. If the measure works in my sample data model but it does not work in your data model, I have no idea but I need to check what is the difference between mine and yours.