Bring back results if a particular date falls between 2 date fields

[bryanmc97]

I would like to bring back the results of my dataset if a particular user-defined date falls between 2 date fields that are a part of the entire dataset.  Not sure if this is possible or not.

[pradeep_kare12]

@bryanmc97  Yes, its possible to filter your dataset to bring back results where user defined date falls between two date fields. If youre working with a SQL database, you can use below query

SELECT *
FROM myTable
WHERE userDefinedDate BETWEEN startDate AND endDate;

Also, you can modify this query according to your data needs.

[bryanmc97]

Thanks @pradeep_kare12 and @amitchandak for your responses.  These help -- to a point.

Let's say I have a job that starts on 10/25/23 (start field) and ends on 10/29/23 (end field).  What I ultimately would like is, if a user enters a date range, say 10/23/23 (start field) and 10/31/23 (end field), that the above job will show up because the user-defined date range contains the job dates within it.  Similarly, if the user chose 10/26/23 and 10/28/23, the job should also show up, as at least one of the dates of the job (a total of 5 days between 10/25/23 and 10/29/23, in this case 10/26/23, 10/27/23, and 10/28/23) is a part of the window chosen by the user.

Hopefully this is not too confusing.  Please reach back out to me if you have any questions.  Thanks for your help.

[v-cgao-msft]

Hi @bryanmc97 ,

First create a unconnected table for the slicer.
Then please create a new measure:

Measure = 
VAR _min_date =
    MIN ( 'Calendar'[Date] )
VAR _max_date =
    MAX ( 'Calendar'[Date] )
VAR _start_date =
    MAX ( 'Table'[start field] )
VAR _end_date =
    MAX ( 'Table'[end field] )
VAR _result =
    IF (
        (_max_date >= _start_date && _max_date <= _end_date)
            || (_min_date >= _start_date && _min_date <= _end_date)
            || (_min_date <= _start_date && _max_date >= _end_date),
        1
    )
RETURN
    _result

And use it as visual's filter:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[bryanmc97]

I think I am getting close, so thank you @v-cgao-msft.  I set up the below slicer, but the data results are not updating with the date range I enter.

 

[amitchandak]

@bryanmc97 , refer the way

Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

or

HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU