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

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

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

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

      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.