Date Range Slicer for Multi-Date Range

[Anonymous]

Problem:

Create a Date Range Slicer that filters Job Sites based on, StartDate and EndDate range.

IF the Date Range Slicer falls within the "StartDate/EndDate" range then we show the Job Site data.

See the "Show?" Column to view 5 examples below.

Fields:

Date Filter Range = An example of the "Date Range Slicer" selection.

Start Date and End Date = The sub-set of date ranges that the slicer needs to evaluate against.

Show? = This is just an example of how the Job Site data should be filtered by the "Date Filter Range" slicer.

Example Data:

Date Filter Range			Start Date	End Date	Show?
1/1/2021		Job Site 1	12/25/2020	1/9/2021	yes
1/2/2021		Job Site 2	1/1/2021	2/1/2021	yes
1/3/2021		Job Site 3	12/22/2020	12/29/2020	no
1/4/2021		Job Site 4	1/21/2020	2/1/2021	yes
1/5/2021		Job Site 5	12/31/2020	2/1/2021	yes
1/6/2021
1/7/2021
1/8/2021
1/9/2021
1/10/2021
1/11/2021
1/12/2021
1/13/2021
1/14/2021
1/15/2021
1/16/2021
1/17/2021
1/18/2021
1/19/2021
1/20/2021
1/21/2021
1/22/2021
1/23/2021
1/24/2021
1/25/2021
1/26/2021
1/27/2021
1/28/2021
1/29/2021
1/30/2021
1/31/2021

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

Create a measure  as below:

Show? = 
var _mindate=MINX(ALLSELECTED('Table'),'Table'[Date Filter Range])
var _maxdate=MAXX(ALLSELECTED('Table'),'Table'[Date Filter Range])
Return
IF((MAX('Table (2)'[End Date]) in FILTERS('Table'[Date Filter Range])&&MAX('Table (2)'[End Date])>=_mindate)||(MAX('Table (2)'[End Date])>=_maxdate&&MAX('Table (2)'[Start Date])<=_maxdate),"yes","no")

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

@Anonymous I would think that something like this would work:

Measure = 
  VAR __MaxDate = MAX('DateTable'[Date])
  VAR __MinDate = MIN('DateTable'[Date])
  VAR __StartDate = MAX('Table'[StartDate])
  VAR __EndDate = MAX('Table'[EndDate])
RETURN
  IF( (__MinDate >= __StartDate && __MinDate <= __EndDate) || (__MaxDate <= __EndDate && __MaxDate >= __StartDate),1,0)

[Jihwan_Kim]

Hi, @Anonymous 

I am not sure if I understood your question correctly. but please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

Show Measure =

IF (

ISINSCOPE(Dates[Date]),

CALCULATE (

CONCATENATEX ( Sites, Sites[Job], ", " ),

FILTER (

Sites,

MIN ( Dates[Date] ) <= Sites[End Date]

&& MAX ( Dates[Date] ) >= Sites[Start Date]

)

)

)

https://www.dropbox.com/s/y77rumwtpvkc80y/cshepler.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Greg_Deckler]

@Anonymous You want a Complex Selector. An example is here. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

Basically a measure that returns 1 or 0 and you filter on it. Specifics vary but I would imagine in your case you would get the MIN and MAX of the date column in your date range slicer and then use a SWITCH or IF statement to determine if it is in range. I wasn't clear on whether just part of it had to be in range or all of it.

[Anonymous]

Greg,

Thank you for the Reply.

Just part of the StartDate/EndDate range needs to be within the "Date Range Slicer" selection to include the job site.