Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
awal2018
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

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

\

 

 

Any tips?

2 ACCEPTED SOLUTIONS

 

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

View solution in original post

@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

View solution in original post

12 REPLIES 12
Vvelarde
Community Champion
Community Champion

@awal2018

 

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

 

Regards

 

Victor




Lima - Peru

yes

Hello,

 

I created this table :

 

001.png

 

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 :

002.png

 

I hope this will help

 

 

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?

 

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

Amazing thank you!

@awal2018 @Salonic

 

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

 

Days.png




Lima - Peru

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

@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

Amazing! 

 

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

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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