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

21/1/2018First Day
21/2/2018MIddle Day
21/3/2018Last Day

\

 

 

Any tips?

  • 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

12 Replies

      • 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