How to use DAX to filter by Booking and Arrival Date dimensions from two tables?

[pjpbarnes]

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.

I originally asked the question on StackOverflow a few days ago and will link between the two if I can find the answer.

[pjpbarnes]

I believe a solution might be two separate measures - testing the below further.  However it would be great to identify a cleaner solution if possible, dual filtering in one measure.

One measure to identify the total bookings for the PY equivalent of the Arrival Date (across all Booking Dates).

PY Arrival Date Bookings:=
IF (
NOT ( ISBLANK ( [Bookings] ) ),
CALCULATE (
    [Bookings],
    FILTER (
        ALL ( 'Arrival Calendar' ),
        'Arrival Calendar'[Arrival Date]
            >= MIN ( 'Arrival Calendar'[Arrival Date]) - 364
            && 'Arrival Calendar'[Arrival Date]
                <= MAX ( 'Arrival Calendar'[Arrival Date] ) - 364
    )
  )
)

Then another measure to filter the PY Arrival Date bookings by PY Booking Date;

PY Bookings:=
IF (
NOT ( ISBLANK ( [PY Arrival Date Bookings] ) ),
CALCULATE (
    [PY Arrival Date Bookings],
    FILTER (
        ALL ( Calendar ),
        Calendar[Date]
            >= MIN ( Calendar[Date] ) - 364
            && Calendar[Date]
                <= MAX ( Calendar[Date] ) - 364
    )
  )
)

Think I might need to remove the ISBLANK rule, but on initial tests it is looking like a possible solution!

[pjpbarnes]

Above didn't work 

It ended up bringing the PY Arrival Date volumes for the same booking date, not the PY booking date...

[Anonymous]

Hey, I saw your yammer post and it led me here.

I've tried to do a similar thing in the past and this link showed promise, but I didn't see it to completion:

https://community.powerbi.com/t5/Desktop/Count-Number-Of-Events-per-date-value-MEASURE/td-p/137156

I would do this to combine the tables and get a count then create a second measure that would do SAMEPERIODLASTYEAR or PARALLELPERIOD

https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

https://docs.microsoft.com/en-us/dax/parallelperiod-function-dax

I don't have a ton of time today to look into this, but let me know if these tips are at all helpful.  If I've misunderstood the objective, could you try rephrasing the question a little more directly for me?  I may be misunderstanding the question.

[pjpbarnes · Accepted solution]

Hey, thanks for looking + suggesting those, will have a good read!

Stupidly, the answer was staring at me all along.  In the DAX in original description, it contained an IF IS BLANK argument, and this was affecting the results.  Once I removed that, all works fine and both tables are filtered!

Many thanks,

Phil

PY 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
    )
)