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 ,

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