Slicing and Filtering records with duration

[roscoegray]

Hi All, 

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 and I can't figure out how I would do it. 

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.

To demonstrate here is some data..

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

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 12:00:00 29/07/2018 and 22:00:00 29/07/2018 i would like to get 9H ONLINE and 1H OFFLINE.

In my real dataset, i have more than two types of events and more assets. 

Any help would be greatly appreciated!!

[v-yulgu-msft]

Hi @roscoegray,

Thanks for sharing the solution.

Regards,

Yuliana Gu

[roscoegray · Accepted solution]

Not my best work but this seems to give me the desired result...

FilteredDuration =
SUMX (
    Events,
    SWITCH (
        TRUE (),
        Events[DateTime] > [MinDateTime]
            && Events[NextDateTime] < [MaxDateTime], Events[Durations (Seconds)],
        Events[DateTime] < [MinDateTime]
            && Events[NextDateTime] > [MinDateTime]
            && Events[NextDateTime] < [MaxDateTime], DATEDIFF ( [MinDateTime], Events[NextDateTime], SECOND ),
        Events[DateTime] > [MinDateTime]
            && Events[DateTime] < [MaxDateTime]
            && Events[NextDateTime] > [MaxDateTime], DATEDIFF ( Events[DateTime], [MaxDateTime], SECOND ),
        0
    )
)

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. 

Please let me know if there is a better way but hopefully, this might help someone at the very least.