Slicing and Filtering records with duration

Question

Hi All,&nbsp;&nbsp;I have a flat file event log that I am importing that has records for many assets over may days. I would like to visualise for any given time frame, whole or part of a day, how much duration that event was active for. I created a calculated column to calculate the duration of each event, however, I have an issue when filtering&nbsp;and I can't figure out how I would do it.&nbsp;&nbsp;The issue comes when i filter, for example, by day. An event that started before midnight would be filtered out. The same applies if i was looking at a period of the day, any events starting before this time would not have their duration included.&nbsp;To demonstrate here is some data..&nbsp;AssetEvent DateEvent TimeEventDuration128/07/201822:00:00ONLINE15:00:00129/07/201813:00:00OFFLINE01:00:00129/07/201814:00:00ONLINE11:00:00130/07/201801:00:00OFFLINE&nbsp;&nbsp;If i was to sum the duration and filter by 29/07/2018 i would get 11H ONLINE and 1H OFFLINE, when what i would like to get is 23H ONLINE and 1H OFFLINE. And if i filter between&nbsp;12:00:00 29/07/2018 and 22:00:00 29/07/2018 i would like to get 9H ONLINE and 1H OFFLINE.&nbsp;In my real dataset, i have more than&nbsp;two types of events and more assets.&nbsp;&nbsp;Any help would be greatly appreciated!!

roscoegray · Accepted Answer

Not my best work but this seems to give me the desired result...&nbsp;FilteredDuration&nbsp;=
SUMX&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;Events,
&nbsp;&nbsp;&nbsp;&nbsp;SWITCH&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;TRUE&nbsp;(),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Events[DateTime]&nbsp;&gt;&nbsp;[MinDateTime]
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;Events[NextDateTime]&nbsp;&lt;&nbsp;[MaxDateTime],&nbsp;Events[Durations&nbsp;(Seconds)],
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Events[DateTime]&nbsp;&lt;&nbsp;[MinDateTime]
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;Events[NextDateTime]&nbsp;&gt;&nbsp;[MinDateTime]
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;Events[NextDateTime]&nbsp;&lt;&nbsp;[MaxDateTime],&nbsp;DATEDIFF&nbsp;(&nbsp;[MinDateTime],&nbsp;Events[NextDateTime],&nbsp;SECOND&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Events[DateTime]&nbsp;&gt;&nbsp;[MinDateTime]
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;Events[DateTime]&nbsp;&lt;&nbsp;[MaxDateTime]
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;Events[NextDateTime]&nbsp;&gt;&nbsp;[MaxDateTime],&nbsp;DATEDIFF&nbsp;(&nbsp;Events[DateTime],&nbsp;[MaxDateTime],&nbsp;SECOND&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0
&nbsp;&nbsp;&nbsp;&nbsp;)
)MaxDateTime = MAXX((DateTable),[Date]) + MAXX((TimeTable),[Time])MinDateTime = MINX((DateTable),[Date]) + MINX((TimeTable),[Time])I have three cases for each depending on if the start or finish time is outside the filter to recalculate the duration.&nbsp;&nbsp;Please let me know if there is a better way but hopefully, this might help someone at the very least.&nbsp;

Forum Discussion

Slicing and Filtering records with duration

2 Replies

Related content

Slice auto filter

notebook duration time issue

Dynamic filtering/slicing if text contains

duration format

Reset Performance analyzer- For consistent duration

Asset	Event Date	Event Time	Event	Duration
1	28/07/2018	22:00:00	ONLINE	15:00:00
1	29/07/2018	13:00:00	OFFLINE	01:00:00
1	29/07/2018	14:00:00	ONLINE	11:00:00
1	30/07/2018	01:00:00	OFFLINE