How to ignore specific slicer with condition and bring data to the same matrix?

[Devtr]

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!

[Devtr]

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.

[Devtr]

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.

@Ashish_Mathur 

Thanks.

[Ashish_Mathur]

Hi,

I just cannot understand your question.  What exactly do you want?

[Devtr]

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.

[Ashish_Mathur]

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.

[Devtr]

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.

[Ashish_Mathur]

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.

[Devtr]

In my excel sheet, their booking date is outside of the slicer booking date range.

Thanks.

[Ashish_Mathur]

Hi,

I am sure someone who understands your requirement will help.

[Devtr]

Thanks @Ashish_Mathur for the attempt.

Appreciate it.