cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Regular Visitor

## 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/2018 First Day 2 1/2/2018 MIddle Day 2 1/3/2018 Last Day

\

Any tips?

2 ACCEPTED SOLUTIONS
Resolver I

Try with this formula :

```Status =
IF ( ISBLANK(DateAdd(Guests[Dates];1;DAY) ) ; "Last Day" ; "Middle Day" ) )```
Community Champion

@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

Lima - Peru
12 REPLIES 12
Community Champion

@awal2018

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

Regards

Victor

Lima - Peru
Regular Visitor

yes

Resolver I

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

Regular Visitor

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?

Resolver I

Try with this formula :

```Status =
IF ( ISBLANK(DateAdd(Guests[Dates];1;DAY) ) ; "Last Day" ; "Middle Day" ) )```
Regular Visitor

Amazing thank you!

Community Champion

Hi, there is a issue in the formula when you have contiguous dates in different GuestID.

Lima - Peru
Regular Visitor

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

Community Champion

@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

Lima - Peru
Regular Visitor

Amazing!

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

Regular Visitor

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

Resolver I

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors