Forum Discussion

awal2018's avatar
awal2018
Regular Visitor
8 years ago
Solved

First Middle Last Dates

I am working for a hotel company, and we have a list of Guest ID's and their Stay dates, I want to determine which stays were a first, middle or last day. ID Stay Date Frist Middle Last 2 1/1/2...
  • Salonic's avatar
    Salonic
    8 years ago

     

    Try with this formula :

     

    Status = 
IF( ISBLANK(DateAdd(Guests[Dates];-1;DAY));"First Day";
	IF ( ISBLANK(DateAdd(Guests[Dates];1;DAY) ) ; "Last Day" ; "Middle Day" ) )
  • Vvelarde's avatar
    Vvelarde
    8 years ago

    awal2018

     

    Hi, try with this calculated column:

     

    Day =
VAR StayDate = Table1[StayDate] - 1
VAR StayDate_plus1 = Table1[StayDate] + 1
RETURN
    IF (
        COUNTROWS (
            FILTER (
                Table1;
                Table1[StayDate] = StayDate
                    && Table1[GuesID] = EARLIER ( Table1[GuesID] )
            )
        )
            < 1;
        "First";
        IF (
            COUNTROWS (
                FILTER (
                    Table1;
                    Table1[StayDate] = StayDate_plus1
                        && Table1[GuesID] = EARLIER ( Table1[GuesID] )
                )
            )
                < 1;
            "Last";
            "Middle"
        )
    )

    Regards

    Victor

Salonic's avatar
Salonic
Resolver I
8 years ago

 

Try with this formula :

 

Status = 
IF( ISBLANK(DateAdd(Guests[Dates];-1;DAY));"First Day";
	IF ( ISBLANK(DateAdd(Guests[Dates];1;DAY) ) ; "Last Day" ; "Middle Day" ) )
awal2018's avatar
awal2018
Regular Visitor
8 years ago

can you post your sample results? my syntax is different than what this sample is