Slicing Between Multiple Dates

[JVal76]

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

[JVal76]

Link has been updated to allow anyone with link.

https://drive.google.com/file/d/1dJ1PXwh4QI6v73-704JaA-MuouzxmrLw/view?usp=drive_link

[Anonymous · Accepted solution]

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

[lbendlin]

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]

My fault, I have edited the op. Thanks

[lbendlin]

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. 

[JVal76]

I don't think I am following. I am not able to find tutorials on the Intersect function as it pertains to dates. Do you happen to have an example I can follow?

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[JVal76]

I have created an example pbix and uploaded to Google Drive, with a description of the desired outcome. Please let me know if you need any more information. Thanks you!

https://drive.google.com/file/d/1dJ1PXwh4QI6v73-704JaA-MuouzxmrLw/view?usp=drive_link