Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Power BI gurus,
The matrix below shows hotel reservations arriving on the arrival dates (based on the slicer called "Reservations Booked From and to" selected dates).
The simple matrix shows all reservations that were booked on the selected booking dates: 04-03-2023 to 04-15-2023
How can I show the rest of the reservations for those same arrival dates right beside the existing columns?
They fall outside of the selected booking dates in the slicer.
So, this should ignore the slicer booking dates and bring the rest of the reservations for those arrival dates.
Those are as below: (They are outside of the selected booking date range)
155 | 5/1/2023 | 5/6/2023 | phone | 159 | 4/30/2023 |
158 | 5/5/2023 | 5/7/2023 | web | 139 | 4/20/2023 |
165 | 5/10/2023 | 5/15/2023 | OTA | 149 | 5/1/2023 |
172 | 5/15/2023 | 5/16/2023 | web | 159 | 4/24/2023 |
And it should include the below reservations:
150 | 5/1/2023 | 5/3/2023 | phone | 179 | 4/6/2023 |
155 | 5/1/2023 | 5/6/2023 | phone | 159 | 4/30/2023 |
158 | 5/5/2023 | 5/7/2023 | web | 139 | 4/20/2023 |
160 | 5/5/2023 | 5/7/2023 | web | 139 | 4/8/2023 |
163 | 5/10/2023 | 5/12/2023 | phone | 155 | 4/10/2023 |
165 | 5/10/2023 | 5/15/2023 | OTA | 149 | 5/1/2023 |
170 | 5/15/2023 | 5/16/2023 | web | 169 | 4/11/2023 |
172 | 5/15/2023 | 5/16/2023 | web | 159 | 4/24/2023 |
180 | 5/20/2023 | 5/21/2023 | phone | 149 | 4/6/2023 |
190 | 5/24/2023 | 5/26/2023 | OTA | 149 | 4/10/2023 |
200 | 5/30/2023 | 5/31/2023 | OTA | 175 | 4/5/2023 |
So, the matrix will show extra columns consisting of all reservations for those arrival dates:
Please download the PBix file and Excel here.
If my SQL query is correct (or close), I am trying to achieve this:
SELECT COUNT(DISTINCT([Reservation_Index])) FROM Reservations
WHERE Arrival_Date IN
(SELECT DISTINCT([Arrival_date]) FROM Reservations
WHERE Booking_Date >= 04/03/2023 AND Booking_Date <= 04/15/2023)
I am trying to get all the bookings from the arrival dates, where these arrival dates received at least one booking between 04/03/2023 and 04/15/2023.
@amitchandak , @Greg_Deckler , @johnt75 , @Ashish_Mathur , @Ritaf1983 , @Ahmedx , @lbendlin
Thank you for your time and help!
This can be done if we supply these arrival dates explicitly with IN operator. Like:
CALCULATE(DISTINCTCOUNT(Reservations[res_index]),
FILTER(ALL('Calendar'[arrival_date]),
'Calendar'[arrival_date] in {Date(05/01/2023), Date(05/05/2023)...}))
But, how do I use a variable that holds all the possible arrival dates dynamically based on the slicer selection and apply that?
Thanks.
If my SQL query is correct (or close), I am trying to achieve this:
SELECT COUNT(DISTINCT([Reservation_Index])) FROM Reservations
WHERE Arrival_Date IN
(SELECT DISTINCT([Arrival_date]) FROM Reservations
WHERE Booking_Date >= 04/03/2023 AND Booking_Date <= 04/15/2023)
I am trying to get all the bookings from the arrival dates, where these arrival dates received at least one booking between 04/03/2023 and 04/15/2023.
Thanks.
Hi,
I just cannot understand your question. What exactly do you want?
My apologies.
The matrix shows the hotel reservations based on the selected booking date range (the reservations that were made during those days).
How do I show total reservations made (inside that date range plus the rest of them) of all time?
The condition is it has to be the same arrival dates that matrix is showing.
And I want to show that in that same matrix, if it's possible.
Thanks.
Hi,
Download the PBI file from here. For now, the result in the 4th column is the same as that of the first but that may be because the data is such. Please put in some actual data and check the result.
Hope this helps.
The pbix has these four reservations below that fall outside of the selected booking dates:
reservation_index arrival_date departure_date category rate booked_on
155 5/1/2023 5/6/2023 phone 159 4/30/2023
158 5/5/2023 5/7/2023 web 139 4/20/2023
165 5/10/2023 5/15/2023 ota 149 5/1/2023
172 5/15/2023 5/16/2023 web 159 4/24/2023
The reservation index 155, 158, 165, and 172 are the ones.
I appreciate your time.
I tried the solution. But it does not show these reservations.
Hi,
I do not understand. If the booking date range selected is April 3 - April 15 2023, then how can the result be of ID's 165 and 155 - their booking date is with the date range selected.
In my excel sheet, their booking date is outside of the slicer booking date range.
Thanks.
Hi,
I am sure someone who understands your requirement will help.