Unique timeline from the overlapping time stamp

[Anonymous]

Hello Everyone,

I am trying to summarize the timeline of resources who are logging in 'downtime' for the applications.

The details from the PowerApps is captured in the below format:

Employee	App Category	Start Time	End Time	Difference	Supervisor	Department	Location	Resolved by
Resource 1	Application 1	2/4/2021 15:35	2/4/2021 15:40	0:05:06	Supervisor 1	Department 1	Chennai	Agent 1
Resource 1	Application 1	2/4/2021 15:35	2/4/2021 15:40	0:05:06	Supervisor 1	Department 1	Chennai	Agent 1
Resource 1	Application 2	2/4/2021 15:35	2/4/2021 15:40	0:05:06	Supervisor 1	Department 1	Chennai	Agent 1
Resource 1	Application 3	2/4/2021 15:35	2/4/2021 15:40	0:05:06	Supervisor 1	Department 1	Chennai	Agent 1
Resource 2	Application 1	2/4/2021 15:41	2/4/2021 15:42	0:00:25	Supervisor 2	Department 2	Chennai	Agent 1
Resource 2	Application 2	2/4/2021 15:42	2/4/2021 15:43	0:00:38	Supervisor 2	Department 2	Chennai	Agent 1
Resource 3	Application 4	2/4/2021 15:52	2/4/2021 16:55	1:03:03	Supervisor 3	Department 3	Chennai	Agent 1
Resource 3	Application 2	2/4/2021 15:52	2/4/2021 16:55	1:03:03	Supervisor 3	Department 3	Chennai	Agent 1
Resource 4	Application 5	2/4/2021 17:58	2/4/2021 17:59	0:00:15	Supervisor 4	Department 4	UK	Agent 1
Resource 5	Application 2	2/4/2021 17:58	2/4/2021 21:25	3:27:06	Supervisor 4	Department 4	UK	Agent 1
Resource 6	Application 6	2/4/2021 17:58	2/4/2021 17:59	0:00:08	Supervisor 2	Department 4	UK	Agent 2
Resource 4	Application 1	2/4/2021 17:58	2/4/2021 21:25	3:26:58	Supervisor 4	Department 4	UK	Agent 1
Resource 5	Application 4	2/4/2021 22:41	2/4/2021 23:22	0:41:43	Supervisor 5	Department 5	Chennai	Agent 1

I tried using @Greg_Deckler 's solution on Properly summing up time spent in overlapping appointments however My end result was something different than expected.

I would like to summarize information based on the Resource name & date as well as the downtime of applications over a period of time.

My expected output could look something like this:

Resource Name	4-Feb Downtime
Resource 1	0:05:06
Resource 2	0:01:03
Resource 3	1:03:03
Resource 4	3:26:58
Resource 5	4:08:49
Resource 6	0:00:08

Can I please ask for your help in this regard?

Sincerely,

Raj

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure as below.

Result Measure = 
SUMX(
    SUMMARIZE(
        'Table',
        'Table'[Employee],
        "Result",
        CALCULATE(
            SUM('Table'[Difference]),
            FILTER(
                'Table',
                MONTH([Start Time])=2&&
                DAY([Start Time])=4
            )
        )
    ),
    [Result]
)

Result:

Or you can create the following calculated table.

Table 2 = 
SUMMARIZE(
        'Table',
        'Table'[Employee],
        "Result",
        CALCULATE(
            SUM('Table'[Difference]),
            FILTER(
                'Table',
                MONTH([Start Time])=2&&
                DAY([Start Time])=4
            )
        )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello Allan,

Thanks for the above solution, however it is not creating a unique timeline from the overlapping timestamp.

If you look at the table, the actual "downtime" for resource 1 on 4th February across four instances is only 5 minutes (as all the time stamp for resource 1 on 4th Feb is repeating between 3.35 pm and 3.40 pm, however, the result is showing 20 minutes which is misleading for the manager as they are coming in the dashboard to identify the "Lost Productivity" due to application outage. Actual DowntimeResult Measure

[v-alq-msft]

Hi, @Anonymous 

I am  sorry for the late reply. Could you show us what the expected result is like? 

Best Regards

Allan

[Vera_33]

Hi @Anonymous 

Only use the Start Time and End Time, so the number is slightly different from the difference, 

M solution:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZPNqsIwEIVfRboWnEwyUbMT3blTXImLIkEL17a01ee3PyJmUivFe/VCFp1Twvkmc2a7DVY2T87Z3g5EMAxmafoT7cMiSuK6BhyBGiGgGAgykjxJQSWBATKgy8/1ObXZJcqTrL6/sGmYFScbF3U5P9o4DqPK6NBou+G/JsCvE8iPE+DLKagWCW8EYJBcAnQJsD+BPwXVIsk7gZy8SyAZgfLsiBNoQ/VghAFZHpdAugSyP4Hf8F8TKEbAYzc2NPGl6X0KguVAuQRVuVk+M6cX7fvmKJrklb3j2FuDXuaameuenUN3/pg5dj8737XuznXz97eeneUe0dt9LE1vu69Es4QP5uSaU3vqdlc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"App Category" = _t, #"Start Time" = _t, #"End Time" = _t, Difference = _t, Supervisor = _t, Department = _t, Location = _t, #"Resolved by" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"App Category", type text}, {"Start Time", type datetime}, {"End Time", type datetime}, {"Difference", type time}, {"Supervisor", type text}, {"Department", type text}, {"Location", type text}, {"Resolved by", type text}}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([Start Time]), type date),
    #"Sorted Rows" = Table.Sort(#"Inserted Date",{{"Start Time", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Employee", "Date"}, {{"allrows", each _, type table [Employee=nullable text, App Category=nullable text, Start Time=nullable datetime, End Time=nullable datetime, Difference=nullable time, Supervisor=nullable text, Department=nullable text, Location=nullable text, Resolved by=nullable text, Date=date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [a=Table.AddIndexColumn([allrows],"Index",0,1),
b=Table.ExpandRecordColumn(
  Table.AddColumn(
   a,
    "MaxTime",
    (OT) =>
      Table.Max(
        Table.SelectColumns(
          Table.SelectRows(a, (IT) => IT[Index] <= OT[Index]),
          "End Time"
        ),
        "End Time"
      )
  ),
  "MaxTime",
  {"End Time"},
  {"MaxTime"}
),
c=Table.AddColumn(
  b,
  "Break",
  each [
    x = try b[Start Time]{[Index] + 1} - [MaxTime] otherwise 0,
    y = Duration.From( if Number.From(x) > 0 then x else 0)
  ][y]
)][c]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Downtime", each List.Max([Custom][End Time])- List.Min([Custom][Start Time])-Duration.From( List.Sum([Custom][Break]))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"allrows", "Custom"})
in
    #"Removed Columns"

DAX solution:

calculated column

Break = 
VAR CurEmployee = overlap[Employee]
VAR CurDate = overlap[Date]
VAR CurrentTime = overlap[Start Time]
VAR PreviousMax =
    CALCULATE (
        MAX ( overlap[End Time] ),
        FILTER (overlap, overlap[Start Time] < CurrentTime && overlap[Date]=CurDate&&overlap[Employee]=CurEmployee)
    )
VAR MaxTime =
    COALESCE( PreviousMax, [End Time])
VAR Break =
    IFERROR(DATEDIFF(MaxTime,CurrentTime,SECOND),0)
RETURN
    IF(Break<0,0,Break)

measure 

Downtime = 
VAR Totalsecs = DATEDIFF(MIN(overlap[Start Time]),MAX(overlap[End Time]),SECOND) - SUM(overlap[Break])
VAR Hours = INT(Totalsecs/3600)
VAR Mins = INT((Totalsecs-Hours*3600)/60)
VAR Secs = Totalsecs-Hours*3600-Mins*60
RETURN
Hours&":"&Mins&":"&Secs

[Anonymous]

Thanks for this solution Vera, I am testing this solution and would share update with you shortly.