I'm struggling to combine multiple filters looking across two tables in a DAX measure. The objective is to return the values in yellow below.
I have an Arrival Sales table. When a customer makes a booking, they will have a date of arrival in our hotel. Therefore we have two Date dimensions in the one table. We need to assess performance for each arrival date year-on-year (and rolled up by arrival month/year if desired), but also based on the booking date (and rolled up by booking month/year if desired).
The relationships of our tables are in the image above. There is a many to one relationship from Arrival Sales Table Booking Date with Calendar Table Date. There is another many to one relationship with Arrival Sales Table Arrival Date and Arrival Calendar Arrival Date.
When considering previous year comparable day, we take away 364 days from the current day, to ensure like-for-like day of the week, i.e. Tuesday 5th February 2019 would be compared with Tuesday 6th February 2018. This is the same for both Booking and Arrival Date.
Trying to create the Prior Year Bookings measure, I cannot get the expected values with the below DAX attempt.
PY Bookings:=
IF (
NOT ( ISBLANK ( [Bookings] ) ),
CALCULATE (
[Bookings],
FILTER (
ALL ( Calendar ),
Calendar[Date]
>= MIN ( Calendar[Date] ) - 364
&& Calendar[Date]
<= MAX ( Calendar[Date] ) - 364
),
FILTER (
ALL ( 'Arrival Calendar' ),
'Arrival Calendar'[Arrival Date]
>= MIN ( 'Arrival Calendar'[Arrival Date]) - 364
&& 'Arrival Calendar'[Arrival Date]
<= MAX ( 'Arrival Calendar'[Arrival Date] ) - 364
)
)
)
Expected results: return the values in yellow, i.e. last years bookings for that equivalent booking date and equivalent arrival date.
Actual results: PY Bookings becomes blank once I add a Calendar Date filter. Without Calendar Date filter, PY Bookings shows all bookings for the PY Arrival Date, across the entire booking period.
