How to filter a dataset with start and end dates

[Gary_in_Ohio]

I am looking for a solution to filtering a dataset that contains start and end dates based on a user selecting one date. 

Below is a example of the type of data that am working with.

Sample Data

Below is a sample report. 

In this example, we want to answer the question "Who was working where on June 1, 2022?"

I want the users to pick one and only one date.  The users will want to change and rechange this date easily. So, I'd prefer it to be in a slicer.  I want to use only on the data where the start comes on or before the user selected date and the end date is on or after the user selected date.

If the users see something incorrect, they will go into the application and make corrections. They will rerun the report and expect see their changes immediately. So, this must work with DirectQuery data.

My actual dataset is much more complex.  But this example gets to the heart of the issue.

[v-easonf-msft · Accepted solution]

Hi, @Gary_in_Ohio 

You can create a measure like:

filter =
IF (
    MAX ( Location[StartDate] ) <= SELECTEDVALUE ( AsOf[Date] )
        && MAX ( Location[EndDate] ) >= SELECTEDVALUE ( AsOf[Date] ),
    1,
    0
)

Then you can apply this mesaure to the table visual filter pane.

Best Regards,
Community Support Team _ Eason

[amitchandak]

@Gary_in_Ohio ,refer

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...