Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Date Range Slicer for Multi-Date Range

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 DateEnd DateShow?
1/1/2021 Job Site 112/25/20201/9/2021yes
1/2/2021 Job Site 21/1/20212/1/2021yes
1/3/2021 Job Site 312/22/202012/29/2020no
1/4/2021 Job Site 41/21/20202/1/2021yes
1/5/2021 Job Site 512/31/20202/1/2021yes
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     
1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

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:

v-kelly-msft_0-1621590138387.png

v-kelly-msft_1-1621590146688.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

5 REPLIES 5
v-kelly-msft
Community Support
Community Support

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:

v-kelly-msft_0-1621590138387.png

v-kelly-msft_1-1621590146688.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

Greg_Deckler
Community Champion
Community Champion

@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)


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Jihwan_Kim
Super User
Super User

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.

 

Picture2.png

 

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]
)
)
)
 
 

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

 


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


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.
Greg_Deckler
Community Champion
Community Champion

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors