Forum Discussion

LaurenceSD's avatar
LaurenceSD
Icon for Advocate II rankAdvocate II
4 years ago
Solved

Find a customers next booking

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 guest...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    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's avatar
Kitty-SD
Frequent Visitor
4 years ago

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's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Kitty-SD
      Frequent Visitor
      4 years ago

      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.