DAX query - should be simple, but I'm missing something

[joyhackett]

I have 2 tables that do not have a relationship:

Date (1/1/2019 - 12/31/2025)
- date_key
- full_date
- EOM Month = FORMAT(DATE(1, MONTH('Date'[full_date]), 1), "mmmm")

Location (sample below)
- location_key
- start_date
- end_date

location sample data:
abc, 1/1/2020, 12/31/9999
lmn, 3/1/2022, 3/31/2022
xyz,  5/1/2022, 12/31/9999

My table should look like this:

EOMonth	Jan 2022	Feb 2022	Mar 2022	Apr 2022	May 2022
location count	1	1	2	1	2

And it does because my measure is this:

location count =

CALCULATE(

    DISTINCTCOUNTNOBLANK(Location[location_key]),

    Location[start_date] <= max('Date'[full_date]) && Location[end_date] >= max('Date'[full_date]))

But now, I want to filter based on the start_date and end_date, so if the user only wants to see locations that started between 3/1 and 5/1, abc will be filtered out and I'm left with:

EOMonth	Jan 2022	Feb 2022	Mar 2022	Apr 2022	May 2022
location count	0	0	1	0	1

I'm not sure how to add my start_date filter to the above DAX and still maintain the ability to show count by month.

All help appreciated! Thanks!

[joyhackett · Accepted solution]

I guess I just needed to write it out 🙂

I'm not sure if this is the best way, but I found that if I add a hidden filter to my table where location_key is not null, then it works.