cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lramsey47 Frequent Visitor
Frequent Visitor

Finding next checkin and last checkout dates

Hello,

I am new to coding and brand new to DAX. I have been successful in transforming some of my data for reporting but am struggling with getting dates related to a property for the next checkin and the last checkout dates for a specific property. Below is an example of the data with the two desired columns filled with the expected data. I have tried some of the examples I have found in the forum to no avail. I tried the following for getting the next checkin date but it displays the same checkin date for the selected reservation. 

NextCheckin =
            CALCULATE (
                     MAX ( arrivals[checkin_date]),
                     FILTER ( ALL (arrivals), EARLIER (arrivals[id]) = arrivals[id]
                     && EARLIER(arrivals[checkin_date]) <= arrivals[checkin_date]
                     && EARLIER(arrivals[Index]) <> arrivals[Index]
            )
)
 
Here is the desired results:
checkin_dateRes IDUnitNightsSpecialRequestscheckout_dateLastCheckoutNextCheckin
6/8/2019 0:006316ELKR7Request16/15/2019 0:00 6/16/2019
6/16/2019 0:006797ELKR6Request16/22/2019 0:006/15/20196/22/2019
6/22/2019 0:006798ELKR7Request16/29/2019 0:006/22/2019 
5/25/2019 0:00907826FCL025Request15/30/2019 0:00 6/30/2018
6/30/2019 0:006277FCL0214Request17/14/2019 0:005/30/20197/20/2019
7/20/2019 0:00907761FCL027Request17/27/2019 0:007/14/20197/27/2019
7/27/2019 0:0018102FCL027Request18/3/2019 0:007/27/2019 
5/23/2019 0:0018061FCL044Request15/27/2019 0:00 6/2/2019
6/2/2019 0:0018077FCL046Request16/8/2019 0:005/27/20196/20/2019
6/20/2019 0:006850FCL047Request16/27/2019 0:006/8/20197/1/2019
7/1/2019 0:0017830FCL0419Request17/20/2019 0:006/27/20197/29/2019
7/1/2019 0:0017830FCL0419Request27/20/2019 0:006/27/20197/29/2019
7/29/2019 0:006523FCL0411Request18/9/2019 0:007/20/2019 
7/29/2019 0:006523FCL0411Request28/9/2019 0:007/20/2019 
5/3/2019 0:00907879FCL111Request15/4/2019 0:00 7/3/2019
7/3/2019 0:00907880FCL1110Request17/13/2019 0:005/4/2019 

 

The ResID is unique to a reservation but there can be two entries for a reservation if there are more than one special request for that particular entry.

 

checkin_date,Res ID,Unit,Nights,SpecialRequests,checkout_date,LastCheckout,NextCheckin,
6/8/2019 0:00,6316,ELKR,7,Request1,6/15/2019 0:00,,6/16/2019,
6/16/2019 0:00,6797,ELKR,6,Request1,6/22/2019 0:00,6/15/2019,6/22/2019,Same Day Turn
6/22/2019 0:00,6798,ELKR,7,Request1,6/29/2019 0:00,6/22/2019,,Same Day Turn
5/25/2019 0:00,907826,FCL02,5,Request1,5/30/2019 0:00,,6/30/2018,
6/30/2019 0:00,6277,FCL02,14,Request1,7/14/2019 0:00,5/30/2019,7/20/2019,
7/20/2019 0:00,907761,FCL02,7,Request1,7/27/2019 0:00,7/14/2019,7/27/2019,
7/27/2019 0:00,18102,FCL02,7,Request1,8/3/2019 0:00,7/27/2019,,Same Day Turn
5/23/2019 0:00,18061,FCL04,4,Request1,5/27/2019 0:00,,6/2/2019,
6/2/2019 0:00,18077,FCL04,6,Request1,6/8/2019 0:00,5/27/2019,6/20/2019,
6/20/2019 0:00,6850,FCL04,7,Request1,6/27/2019 0:00,6/8/2019,7/1/2019,
7/1/2019 0:00,17830,FCL04,19,Request1,7/20/2019 0:00,6/27/2019,7/29/2019,
7/1/2019 0:00,17830,FCL04,19,Request2,7/20/2019 0:00,6/27/2019,7/29/2019,
7/29/2019 0:00,6523,FCL04,11,Request1,8/9/2019 0:00,7/20/2019,,
7/29/2019 0:00,6523,FCL04,11,Request2,8/9/2019 0:00,7/20/2019,,
5/3/2019 0:00,907879,FCL11,1,Request1,5/4/2019 0:00,,7/3/2019,
7/3/2019 0:00,907880,FCL11,10,Request1,7/13/2019 0:00,5/4/2019,,

 

Any help would be much appreciated!

Cheers!

L:L

 

1 ACCEPTED SOLUTION

Accepted Solutions
lramsey47 Frequent Visitor
Frequent Visitor

Re: Finding next checkin and last checkout dates

I think I got it!

LastCheckout =

CALCULATE (

    MAX ( arrivals[checkout_date] ),

    arrivals[checkout_date] < EARLIER ( arrivals[checkout_date] ),

    ALLEXCEPT ( arrivals, arrivals[the_clear_creek_group.units.unit_code] )

)

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Finding next checkin and last checkout dates

Hi @lramsey47 ,

Try this:

NextCheckin =
CALCULATE (
    MIN ( arrivals[checkin_date] ),
    arrivals[checkin_date] > EARLIER ( arrivals[checkin_date] ),
    ALLEXCEPT ( arrivals, arrivals[Unit] )
)
LastCheckout =
CALCULATE (
    MAX ( arrivals[checkout_date] ),
    arrivals[checkout_date] < EARLIER ( arrivals[checkout_date] ),
    ALLEXCEPT ( arrivals, arrivals[Unit] )
)

 

lramsey47 Frequent Visitor
Frequent Visitor

Re: Finding next checkin and last checkout dates

Outstanding! Thank you so much! I am going to play with this a bit but it appears this is has me fixed.

Cheers!

 

lramsey47 Frequent Visitor
Frequent Visitor

Re: Finding next checkin and last checkout dates

Can I also use this to find the type of reservation it was and the guest as well? 

lramsey47 Frequent Visitor
Frequent Visitor

Re: Finding next checkin and last checkout dates

After looking more closely, it is displaying the checkin date as the last checkout date rather than the true last checkout date:

checkin_dateMSTcheck-inRes IDUnitNightscheckout_dateMSTcheck-outLastCheckoutNextCheckin
6/8/2019 0:006/8/2019 16:006316ELKR76/15/2019 0:006/15/2019 10:006/8/2019 0:006/16/2019 0:00
6/16/2019 0:006/16/2019 17:006797ELKR66/22/2019 0:006/22/2019 11:006/16/2019 0:006/22/2019 0:00
6/22/2019 0:006/22/2019 17:006798ELKR76/29/2019 0:006/29/2019 11:006/22/2019 0:00 
5/25/2019 0:005/25/2019 16:00907826FCL0255/30/2019 0:005/30/2019 10:005/25/2019 0:006/30/2019 0:00
6/30/2019 0:006/30/2019 16:006277FCL02147/14/2019 0:007/14/2019 10:006/30/2019 0:007/20/2019 0:00
7/20/2019 0:007/20/2019 16:00907761FCL0277/27/2019 0:007/27/2019 10:007/20/2019 0:007/27/2019 0:00
7/27/2019 0:007/27/2019 16:0018102FCL0278/3/2019 0:008/3/2019 10:007/27/2019 0:008/10/2019 0:00
5/23/2019 0:005/23/2019 16:0018061FCL0445/27/2019 0:005/27/2019 10:005/23/2019 0:006/2/2019 0:00
6/2/2019 0:006/2/2019 16:0018077FCL0466/8/2019 0:006/8/2019 10:006/2/2019 0:006/20/2019 0:00
6/20/2019 0:006/20/2019 16:006850FCL0476/27/2019 0:006/27/2019 10:006/20/2019 0:006/29/2019 0:00
6/20/2019 0:006/20/2019 16:006850FCL0476/27/2019 0:006/27/2019 10:006/20/2019 0:006/29/2019 0:00
7/1/2019 0:007/1/2019 16:0017830FCL04197/20/2019 0:007/20/2019 10:007/1/2019 0:007/20/2019 0:00
7/1/2019 0:007/1/2019 16:0017830FCL04197/20/2019 0:007/20/2019 10:007/1/2019 0:007/20/2019 0:00
7/29/2019 0:007/29/2019 16:006523FCL04118/9/2019 0:008/9/2019 10:007/29/2019 0:008/24/2019 0:00
7/29/2019 0:007/29/2019 16:006523FCL04118/9/2019 0:008/9/2019 10:007/29/2019 0:008/24/2019 0:00
5/3/2019 0:005/3/2019 16:00907879FCL1115/4/2019 0:005/4/2019 10:005/3/2019 0:007/3/2019 0:00
7/3/2019 0:007/3/2019 16:00907880FCL11107/13/2019 0:007/13/2019 10:007/3/2019 0:0012/20/2019 0:00

 

 

lramsey47 Frequent Visitor
Frequent Visitor

Re: Finding next checkin and last checkout dates

I think I got it!

LastCheckout =

CALCULATE (

    MAX ( arrivals[checkout_date] ),

    arrivals[checkout_date] < EARLIER ( arrivals[checkout_date] ),

    ALLEXCEPT ( arrivals, arrivals[the_clear_creek_group.units.unit_code] )

)

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 457 members 4,659 guests
Please welcome our newest community members: