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.
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. 
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
