Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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     
Need Help
Tutorial Requests
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

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

    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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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)
  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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!