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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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'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! 😊