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

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] )
  • 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