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.
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.
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 |
6/22/2019 0:00 | 6798 | ELKR | 7 | Request1 | 6/29/2019 0:00 | 6/22/2019 | |
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 | |
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 |
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
Solved! Go to Solution.
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] )
)
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] ) )
Outstanding! Thank you so much! I am going to play with this a bit but it appears this is has me fixed.
Cheers!
Can I also use this to find the type of reservation it was and the guest as well?
After looking more closely, it is displaying the checkin date as the last checkout date rather than the true last checkout date:
checkin_date | MSTcheck-in | Res ID | Unit | Nights | checkout_date | MSTcheck-out | LastCheckout | NextCheckin |
6/8/2019 0:00 | 6/8/2019 16:00 | 6316 | ELKR | 7 | 6/15/2019 0:00 | 6/15/2019 10:00 | 6/8/2019 0:00 | 6/16/2019 0:00 |
6/16/2019 0:00 | 6/16/2019 17:00 | 6797 | ELKR | 6 | 6/22/2019 0:00 | 6/22/2019 11:00 | 6/16/2019 0:00 | 6/22/2019 0:00 |
6/22/2019 0:00 | 6/22/2019 17:00 | 6798 | ELKR | 7 | 6/29/2019 0:00 | 6/29/2019 11:00 | 6/22/2019 0:00 | |
5/25/2019 0:00 | 5/25/2019 16:00 | 907826 | FCL02 | 5 | 5/30/2019 0:00 | 5/30/2019 10:00 | 5/25/2019 0:00 | 6/30/2019 0:00 |
6/30/2019 0:00 | 6/30/2019 16:00 | 6277 | FCL02 | 14 | 7/14/2019 0:00 | 7/14/2019 10:00 | 6/30/2019 0:00 | 7/20/2019 0:00 |
7/20/2019 0:00 | 7/20/2019 16:00 | 907761 | FCL02 | 7 | 7/27/2019 0:00 | 7/27/2019 10:00 | 7/20/2019 0:00 | 7/27/2019 0:00 |
7/27/2019 0:00 | 7/27/2019 16:00 | 18102 | FCL02 | 7 | 8/3/2019 0:00 | 8/3/2019 10:00 | 7/27/2019 0:00 | 8/10/2019 0:00 |
5/23/2019 0:00 | 5/23/2019 16:00 | 18061 | FCL04 | 4 | 5/27/2019 0:00 | 5/27/2019 10:00 | 5/23/2019 0:00 | 6/2/2019 0:00 |
6/2/2019 0:00 | 6/2/2019 16:00 | 18077 | FCL04 | 6 | 6/8/2019 0:00 | 6/8/2019 10:00 | 6/2/2019 0:00 | 6/20/2019 0:00 |
6/20/2019 0:00 | 6/20/2019 16:00 | 6850 | FCL04 | 7 | 6/27/2019 0:00 | 6/27/2019 10:00 | 6/20/2019 0:00 | 6/29/2019 0:00 |
6/20/2019 0:00 | 6/20/2019 16:00 | 6850 | FCL04 | 7 | 6/27/2019 0:00 | 6/27/2019 10:00 | 6/20/2019 0:00 | 6/29/2019 0:00 |
7/1/2019 0:00 | 7/1/2019 16:00 | 17830 | FCL04 | 19 | 7/20/2019 0:00 | 7/20/2019 10:00 | 7/1/2019 0:00 | 7/20/2019 0:00 |
7/1/2019 0:00 | 7/1/2019 16:00 | 17830 | FCL04 | 19 | 7/20/2019 0:00 | 7/20/2019 10:00 | 7/1/2019 0:00 | 7/20/2019 0:00 |
7/29/2019 0:00 | 7/29/2019 16:00 | 6523 | FCL04 | 11 | 8/9/2019 0:00 | 8/9/2019 10:00 | 7/29/2019 0:00 | 8/24/2019 0:00 |
7/29/2019 0:00 | 7/29/2019 16:00 | 6523 | FCL04 | 11 | 8/9/2019 0:00 | 8/9/2019 10:00 | 7/29/2019 0:00 | 8/24/2019 0:00 |
5/3/2019 0:00 | 5/3/2019 16:00 | 907879 | FCL11 | 1 | 5/4/2019 0:00 | 5/4/2019 10:00 | 5/3/2019 0:00 | 7/3/2019 0:00 |
7/3/2019 0:00 | 7/3/2019 16:00 | 907880 | FCL11 | 10 | 7/13/2019 0:00 | 7/13/2019 10:00 | 7/3/2019 0:00 | 12/20/2019 0:00 |
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] )
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |