HELP - A Slicer filtering TWO dates columns using OR instead AND condition.

[Anonymous]

Hi Community,

I have a challenge that I've trying to solve for the last couple days and it's been very hard. 

I need to use a data filter in my slicer as between to filter 2 dates (start_date and finish_date)

This is sample of the data I have.

project	start_date	finish_date	value
A	1/09/2017	31/03/2020	$         59,000,000.00
B	1/09/2015	1/10/2016	$         45,000,000.00
C	25/05/2018	25/05/2022	$         35,000,000.00

Let's say for project A when I use the date slicer with any dates between 01/09/2017 and 31/03/2020 the Project should bring project A.

However my slicer is connected with my start_date so if I put 02/02/2019 to any date it won't appear because 02/02/2019 is one more day then 01/09/2017. 

So I need this slicer to use finish_date as well, more specifically any date between 01/09/2017 and 31/03/2020 but I only have two values and not a list of dates.

I tried:

List (that will duplicated my values and even thought if I use max It will mess up with another table)

USERELATIONSHIP between 'dim_calendar[date] and start_date, finish_date (this solution uses AND instead OR so it didn't work)

BETWEEN DATES (must be an expression for start_date and finish_date i don't know if I can use it)

some help?

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

Table:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

There is no relationship between two tables.

You may create a measure to control the display of the visual as below.

Isdisplay = 
var _date = SELECTEDVALUE('Calendar'[Date])
var _startdate = SELECTEDVALUE('Table'[start_date])
var _enddate = SELECTEDVALUE('Table'[finish_date])
return
IF(
    _date>=_startdate&&_date<=_enddate,
    1,0
)

Then you need to put the measure in the visual level filter and use the date column from 'Calendar' table to filter the result.

Best Regards

Allan

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

[Greg_Deckler]

Use a disconnected calendar table then write a measure along the lines of:

Complex Selector = 

  VAR __Date = SELECTEDVALUE('Calendar Disconnected'[Date])

  VAR __Start = MAX('Table'[start_date])

  VAR __End = MAX('Table'[finisih_date])

RETURN

  IF(__Date >= __Start && __Date <= __End,1,0)

Use this to filter your visual. Attached an exmple PBIX from a similar case that needed a complex selector.

[Anonymous]

Hi Guys,

Thanks for the help. I ended using something similiar in DAX.

Measure = CALCULATE(SUM(table[jmetric]),

FILTER(Fact_job_cost_master,

Fact_job_cost_master[jcm_start_date] <= MAX(Dim_Calendar[Date]) && Fact_job_cost_master[jcm_start_date] >= MIN(Dim_Calendar[Date]) || Fact_job_cost_master[finish_date] <= MAX(Dim_Calendar[Date]) && Fact_job_cost_master[finish_date] >= MIN(Dim_Calendar[Date])))