Find a customers next booking

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 )

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! 😊

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 

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