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

Vvelarde's avatar
Vvelarde
Community Champion
8 years ago

awal2018

 

if the guest stay 5 days the middle are days 2,3,4?

 

Regards

 

Victor

awal2018's avatar
awal2018
Regular Visitor
8 years ago

yes

  • Salonic's avatar
    Salonic
    Resolver I
    8 years ago

    Hello,

     

    I created this table :

     

     

    Created this column :

    Status = 
IF( Guests[Dates]=CALCULATE ( MINX( Guests; Guests[Dates]) ; ALLEXCEPT ( Guests ; Guests[Guests] ) );"First Day";
	IF( Guests[Dates]=CALCULATE ( MAXX( Guests; Guests[Dates]) ; ALLEXCEPT ( Guests ; Guests[Guests] ) ); "Last Day"
	;"Middle Day" ))

    The result :

     

    I hope this will help

     

     

    • awal2018's avatar
      awal2018
      Regular Visitor
      8 years ago

      All of those days you provided should actually be listed as first days becuase the previous day they did not stay with us. 

       

      if the guest stays with us on the 1st, 2nd and 3rd the 1st should be a first day the 2nd a Middle and the 3rd day a last.

       

      Then if the same guest stays with us on the 5th-6th the 5th should be a new first day then the 6th a last day.

       

      Does that make sense?

      • 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" ) )