cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

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 Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 470 members 4,983 guests
Please welcome our newest community members: