Forum Discussion
Overlapping Dates and Service Type
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!
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
8 Replies
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
Brill, thank you! I'll test it out and get back to you..thanks again!
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
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])))))RETURNSUMX ( GROUPBY ( NewTable, [ClientID], [Overlapping] ), [Overlapping] )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
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
