Forum Discussion

JVal76's avatar
JVal76
New Member
1 year ago
Solved

Slicing Between Multiple Dates

I have a dataset with ServiceStart and a ServiceEnd date columns.

I need a date slicer that will include records that would be considered 'active' based on the date selection.

 

In this example if I were to select 3/1/2021 - 4/1/2021, then the records in the screen shot would be shown since the service start is prior and service end date is not until 1/31/2022.

 

I have scoured the internet with no luck on finding a solution and i have to imagine this is a common need.

 

Thanks!

joe

Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi JVal76 ,

    You can follow the steps below to get it, please find the details in the attachment.

    1. Create a measure as below

    Flag = 
VAR _clientid =
    SELECTEDVALUE ( 'IntersectData'[ClientID] )
VAR _year =
    SELECTEDVALUE ( 'Dates'[Date].[Year] )
VAR _month =
    SELECTEDVALUE ( 'Dates'[Date].[MonthNo] )
VAR _yearmonth =
    VALUE ( _year & IF ( _month < 10, "0" & _month, _month ) )
VAR _client =
    CALCULATE (
        MAX ( 'IntersectData'[ClientID] ),
        FILTER (
            'IntersectData',
            'IntersectData'[ClientID] = _clientid
                && VALUE ( FORMAT ( 'IntersectData'[ServiceStart], "YYYYMM" ) ) <= _yearmonth
                && VALUE ( FORMAT ( 'IntersectData'[ServiceEnd], "YYYYMM" ) ) >= _month
        )
    )
RETURN
    IF ( NOT ( ISBLANK ( _client ) ), 1 )

    2. Apply a visual-level filter on the table visual

    Best Regards

8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago
    In this example if I were to select 3/1/2022 - 4/1/2022, then the records in the screen shot would be shown since the service start is prior and service end date is not until 1/31/2022.

    This is confusing to me. Please elaborate - i thought they should not be shown as the selection interval is past the ServiceEnd date.

    • JVal76's avatar
      JVal76
      New Member
      1 year ago

      My fault, I have edited the op. Thanks

      • lbendlin's avatar
        lbendlin
        Super User
        1 year ago

        Use INTERSECT to find if there are any overlapping days between your chosen interval and the service period. If yes, show the data, otherwise don't. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi JVal76 ,

      You can follow the steps below to get it, please find the details in the attachment.

      1. Create a measure as below

      Flag = 
VAR _clientid =
    SELECTEDVALUE ( 'IntersectData'[ClientID] )
VAR _year =
    SELECTEDVALUE ( 'Dates'[Date].[Year] )
VAR _month =
    SELECTEDVALUE ( 'Dates'[Date].[MonthNo] )
VAR _yearmonth =
    VALUE ( _year & IF ( _month < 10, "0" & _month, _month ) )
VAR _client =
    CALCULATE (
        MAX ( 'IntersectData'[ClientID] ),
        FILTER (
            'IntersectData',
            'IntersectData'[ClientID] = _clientid
                && VALUE ( FORMAT ( 'IntersectData'[ServiceStart], "YYYYMM" ) ) <= _yearmonth
                && VALUE ( FORMAT ( 'IntersectData'[ServiceEnd], "YYYYMM" ) ) >= _month
        )
    )
RETURN
    IF ( NOT ( ISBLANK ( _client ) ), 1 )

      2. Apply a visual-level filter on the table visual

      Best Regards