Forum Discussion

ConfusedTime's avatar
ConfusedTime
Frequent Visitor
3 years ago
Solved

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 woul...
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
ConfusedTime
Frequent Visitor
3 years ago

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!

ClientIDServiceStart DateEnd Date
11111Service A18/11/201826/11/2018
11111Service A11/05/2019 
11111Service A19/06/202110/07/2021
11131Service A27/12/201616/06/2017
11131Service B13/06/201723/09/2017
11131Service A24/09/201721/07/2018
11131Service A22/07/201802/10/2018
11131Service A14/12/202004/01/2021
11131Service A03/01/2021 
11222Service A10/08/201324/11/2013
11222Service A10/08/201306/04/2014
11222Service A21/11/201306/04/2014
11222Service B07/04/201417/02/2015
18888Service A11/04/201109/03/2014
18888Service A10/03/201417/08/2014
26262Service A16/09/201311/10/2013
26262Service B12/10/202020/10/2020
26262Service A18/10/202001/11/2020
91919Service A18/03/201302/09/2013
91919Service A07/04/201412/10/2016
91919Service B10/10/2016 
99991Service A24/10/2019 
99991Service A07/12/202025/06/2021
99991Service B25/06/202102/07/2021
99991Service A03/07/202114/09/2021
99991Service A26/01/202214/09/2022
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    ConfusedTime
    Frequent Visitor
    3 years ago

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