Filter two dates with just one filter

[Fátima]

Hi!

I need to filter two columns by one given date. For example, I have registries with the columns "START_DATE" and "END_DATE", so by filtering by date it would return the registries which start_date is before the filter date, and the end date is after the filter date. In other words, I need the registries in which dates range is the date of the filter.

Is this possible to do with a date slicer? I have tried to create one and set both fields to filter, but it doesn't allow me to use the slider nor to set different options (greater than and smaller than) for each column. If not, how can I set a filter that allows me to do this?

Thanks! Regards!

[v-zhangti · Accepted solution]

Hi, @Fátima 

You can try the following methods.

Sample data:

New table:

Date = CALENDAR(MIN('Table'[START_DATE]),MAX('Table'[END_DATE]))

Measure =
IF ( SELECTEDVALUE ( 'Date'[Date] ) = BLANK (), 1,
    IF ( SELECTEDVALUE ( 'Date'[Date] ) >= SELECTEDVALUE ( 'Table'[START_DATE] )
         && SELECTEDVALUE ( 'Date'[Date] ) <= SELECTEDVALUE ( 'Table'[END_DATE] ),
        1,
        0
    )
)

Result:

Hope this method helps you.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Fátima , refer to this example, it uses a date table, two dates

Power BI: 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...

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...