Overlapping Time Problem

[jguercio]

Hi All,

I will start with the obligatory, I am new to this and crawling up the learning curve.
I am having an issue handling overlapping time and need a way of appropriately dealing with the durations (either summing them, using a max, etc.). Basically, when an employee (or more than one employee) writes a note for the same client on the same day, if the times overlap, a rule needs to be put in place to handle aggregating them. The chart below illustrates the issue and what durations we need to use for billing. As an example, in the first condition, with the same start times (SE) and different end times (DE) we use the row with the MAX duration (yellow). 

I have a FACT table set up like this:
FACT-DD Services:

ClientID

Contact Date

Start Time

End Time

Duration (Minutes)

(etc)

That I have summarized this table by day for other purposes using this:

DD Billing PS = CALCULATETABLE(
                        SUMMARIZE(
                            'FACT-DD Services'
                            ,'FACT-DD Services'[Client ID]
                            ,'FACT-DD Services'[First Name]
                            ,'FACT-DD Services'[Last Name]
                            ,'FACT-DD Services'[Contact Date]
                            ,'FACT-DD Services'[Start Time]
                            ,'FACT-DD Services'[End Time]
                            ,'FACT-DD Services'[Created By]
                            ,"Contact Note"
                                ,CALCULATE(
                                    SUMX('FACT-DD Services'
                                    ,'FACT-DD Services'[Duration (Minutes)])
                                    ,'FACT-DD Services'[Note Type]="Contact Note"
                                    )
                            ,"Service Linked Note"
                                ,CALCULATE(
                                    SUMX('FACT-DD Services'
                                    ,'FACT-DD Services'[Duration (Minutes)])
                                    ,'FACT-DD Services'[Note Type]="Service/Treatment Plan Linked"
                                )

 And used this on the summarized table to identify overlaps:

Overlaping Documentation = 
VAR Overlapped=
              COUNTROWS(
                      FILTER(
                               ALL('DD Billing PS'),
                               'DD Billing PS'[Start Time] <= EARLIER('DD Billing PS'[Start Time]) &&
                               'DD Billing PS'[End Time] >= EARLIER('DD Billing PS'[End Time]) &&
                               'DD Billing PS'[Contact Date] = EARLIER('DD Billing PS'[Contact Date]) &&
                               'DD Billing PS'[Start Time] <> Earlier('DD Billing PS'[End Time]) &&
                               'DD Billing PS'[Client ID] = EARLIER('DD Billing PS'[Client ID])
                               )
                    )
RETURN
IF(Overlapped>1, "YES", "NO")

 Any help or support is greatly appreciated!

[Stachu]

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1	Column2
A	1
B	2.5