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

rohit_singh's avatar
rohit_singh
Icon for Solution Sage rankSolution Sage
4 years ago

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! ðŸ˜Š