Overlapping Dates and Service Type

[ConfusedTime]

Hi Everyone,

I'm after some help if possible, I've been through previous posts and I'm stuck! 

I'm trying to identify an overlap in services by date and service type, so for example this would return client 1 as they are accessing 2 different services at the same time:

service table

ClientID    Start Date    End Date   Service

1                1/1/22         31/1/22     A 

2                3/3/18        15/3/18      A

2                4/3/18         5/3/13       A

3                10/10/22                      C

1                 30/1/22        5/2/22    B

Not all records have an end date and the table can have multiple entries for each client.

Any pointers, hints or tips will be a massive help as I'm lost - thanks in advance!

[ConfusedTime]

Hi,

Sorry for the late reply, it doesn't seem to be working for me, I get a massive long number back? 

I've filtered my table to have only 2 service types and put it in your PBI example and get the same result.

 I have this working to count overlapping dates (taken from another post) but I need it to only count the overlapping date when the service is different - can I add yours in to this somehow? Sorry I'm not very good with DAX/variables.  My table is now filtered to be only 2 different types of services.

Thanks

Overlapping Date =

VAR NewTable =

    ADDCOLUMNS (

        'Service',

        "Service", COUNTROWS (

            FILTER (

                ALL ('Service' ),

                [ClientID] = EARLIER ( [ClientID] )

                    && 'Service' [Start Date] < EARLIER ( 'Service'[End Date])

                    && 'Service'[End Date]> EARLIER ( 'Service' [Start Date])

                    && NOT (

                        'Service'[End Date] = EARLIER ( 'Service'[End Date] )

                            && 'Service' [Start Date] = EARLIER ( 'Service' [Start Date])

                    )

            )

        )

    )

RETURN

    SUMX ( GROUPBY ( NewTable, [ClientID], [Overlapping] ), [Overlapping] )

[v-yiruan-msft]

Hi @ConfusedTime ,

What's your expected result? Do you want to get the count of IDs or the IDs which fulfill the conditions? Could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[ConfusedTime]

Thanks for your reply.  I'm hoping to be able to give a count of overlaps, plus identify who they are so they can be followed up. 

I've attached a sample of data, in this example it would highlight 4 overlaps of service (highlighted blue). 

Thanks for all your help!

ClientID	Service	Start Date	End Date
11111	Service A	18/11/2018	26/11/2018
11111	Service A	11/05/2019
11111	Service A	19/06/2021	10/07/2021
11131	Service A	27/12/2016	16/06/2017
11131	Service B	13/06/2017	23/09/2017
11131	Service A	24/09/2017	21/07/2018
11131	Service A	22/07/2018	02/10/2018
11131	Service A	14/12/2020	04/01/2021
11131	Service A	03/01/2021
11222	Service A	10/08/2013	24/11/2013
11222	Service A	10/08/2013	06/04/2014
11222	Service A	21/11/2013	06/04/2014
11222	Service B	07/04/2014	17/02/2015
18888	Service A	11/04/2011	09/03/2014
18888	Service A	10/03/2014	17/08/2014
26262	Service A	16/09/2013	11/10/2013
26262	Service B	12/10/2020	20/10/2020
26262	Service A	18/10/2020	01/11/2020
91919	Service A	18/03/2013	02/09/2013
91919	Service A	07/04/2014	12/10/2016
91919	Service B	10/10/2016
99991	Service A	24/10/2019
99991	Service A	07/12/2020	25/06/2021
99991	Service B	25/06/2021	02/07/2021
99991	Service A	03/07/2021	14/09/2021
99991	Service A	26/01/2022	14/09/2022

[v-yiruan-msft · Accepted solution]

Hi @ConfusedTime ,

You can create a calculated column as below to get it:

Flag = 
CALCULATE (
    DISTINCTCOUNT ( 'Service'[Service] ),
    FILTER (
        'Service',
        [ClientID] = EARLIER ( [ClientID] )
            && 'Service'[Start Date] > EARLIER ( 'Service'[Start Date] )
            && 'Service'[Start Date] <= EARLIER ( 'Service'[End Date] )
            && 'Service'[Service] <> EARLIER ( 'Service'[Service] )
    )
)

Best Regards

[ConfusedTime]

This works perfectly! Thank you so much for all you help! 😊

[ConfusedTime]

Brill, thank you! I'll test it out and get back to you..thanks again!

[v-yiruan-msft]

Hi @ConfusedTime ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards

[v-yiruan-msft]

Hi @ConfusedTime ,

You can create a measure as below to get it:

Clients with multiple services = 
VAR _tab =
    SUMMARIZE (
        'service',
        'service'[ClientID],
        "@count",
            CALCULATE (
                DISTINCTCOUNT ( service[Service] ),
                ALLEXCEPT ( 'service', 'service'[ClientID] )
            )
    )
RETURN
    CONCATENATEX ( FILTER ( _tab, [@count] > 1 ), [ClientID], "," )

If the above one is not what you want, could you please provide your expected result with the detailed calculation logic and specific examples? It would be helpful to find the solution. Thank you.

Best Regards