Creating a measure based on filters from other columns

[dresinger]

Appologies from the jump as I am new to Power BI.

I am trying to create a measure or column that will pull data from other rows in my data table.  This is what I currently have, how my data is structured and what I am looking to get:

I have a table that tracks "memos" entered into a system in chronological order.  These "memos" are given a sequential, unique ID by the system and also time stamped.  Table is structured like this:

memo_id	timepoint	message
1	1622815423	Drill Started
2	1622815596	Drill Stopped
3	1622815620	Drill Started
4	1622816021	Drill Stopped

I added a calculated column to convert timepoint (unix time) to date/time and now have this:

memo_id	timepoint	message	date/time
1	1622815423	Drill Started	6/4/2021 2:03:43PM
2	1622818287	Drill Stopped	6/4/2021 2:51:27PM
3	1622818331	Drill Started	6/4/2021 2:52:11PM
4	1622820922	Drill Stopped	6/4/2021 3:35:22PM

What I need to so is add 3 columns that signify the start, stop and duration of a drill.  I already added the start time by using this formula in a measure:   

Drill Start Date/Time = if(CONTAINSSTRING('memos_view'[message],"drill started")=TRUE(),'memos_view'[Date/Time])

I now need to look for the next "drill stopped" message and copy that date/time to the same row and also calculate the duration.  My desired table should look like this:

memo_id	timepoint	message	date/time	Drill Started	Drill Stopped	Duration
1	1622815423	Drill Started	6/4/2021 2:03:43PM	6/4/2021 2:03:43PM	6/4/2021 2:51:27PM	0:0:47:44
2	1622818287	Drill Stopped	6/4/2021 2:51:27PM
3	1622818331	Drill Started	6/4/2021 2:52:11PM	6/4/2021 2:52:11PM	6/4/2021 3:35:22PM	0:0:43:11
4	1622820922	Drill Stopped	6/4/2021 3:35:22PM

Any help is appreciated!!!

[v-shex-msft]

HI @dresinger,

Any update for these? Did these suggestions help with your situation? If that is the case, you can consider Kudo or accept it to help others who have the same requirement.
If the above does not help, please share more detailed information to help us clarify this scenario.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[v-shex-msft]

HI @dresinger,

You can use the following calculated column expression to get the next stop DateTime based on the current DateTime.
After these steps, you can create a new column with 'datediff' function to calculate the diff between two DateTime fields.

Drill Stopped =
MINX (
    FILTER (
        Table,
        [date/time] > EARLIER ( Table[date/time] )
            && [message] <> "Drill Started"
    ),
    [date/time]
)

Regards,

Xiaoxin Sheng