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

awal2018's avatar
awal2018
Regular Visitor
8 years ago

you are right :( . I saw this when I applied to my larger dataset, any way to work around?

Vvelarde's avatar
Vvelarde
Community Champion
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

  • awal2018's avatar
    awal2018
    Regular Visitor
    8 years ago

    Amazing! 

     

    Any way to do this in DAX without a calucated column. I understand if not. Thanks so much for your help! lifesaver!