How to calculate duration across rows

[BDHicks]

Hi, all.

I'm new to Power BI and could use some advice on calculating duration across rows. Ideally, I'd add an additional column where the duration of an event would be calculated and stored with the "stop" event (Door Forced Open Canceled).

In a typical dataset, I have events occuring within the same second, so they often appear out-of-order if sorted by time. I could likely solve this as the SQL timestamps have ms resolution, but I haven't figured out an efficient way to access the ms value within PBI. So my other option is to utilize other data that can be used to determine the proper sequence, as each event is associated with a specific device (Panel ID), which maintains its own internal serialization data (Panel Index).

I'm typically dealing with datasets that run from 2-60 million rows, so at the end of the day, I'm looking for the most efficient method for calculating the duration between the start and stop events. A sanitized version of typical data is pictured below, and any advice is welcomed. 🙂

[v-yanjiang-msft · Accepted solution]

Hi @BDHicks ,

According to your description, if you want to calculate the duration for each panel ID, here's my solution. Create a measure.

Period =
VAR _MIN =
    MINX (
        FILTER ( 'Table', 'Table'[Panel ID] = MAX ( 'Table'[Panel ID] ) ),
        'Table'[Event Time UTC]
    )
VAR _MAX =
    MAXX (
        FILTER ( 'Table', 'Table'[Panel ID] = MAX ( 'Table'[Panel ID] ) ),
        'Table'[Event Time UTC]
    )
RETURN
    DATEDIFF ( _MIN, _MAX, SECOND )

In my sample below it get the correct result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[MFelix]

Hi @BDHicks,

To what I can see from the data example yhou provided you have multiple values at the same second, however there is different levels of granularity in this case you have Panel ID and the Reader, have you tried making the sort by those 3 columns and then calculating the values of the duration?