Date difference hh:mm between 2 rows with unique ID

trying to determine the length of time between the start and completion of a registration.
Each registration has a unique Event_ID and a DTTM stamp and an event type “reg start”, “reg complete”. For each ID I would like the “length of time” in mm:ss or just minutes.
I am including the file

@lizbethl4 ,

Can you please resend the file? It didn’t come through above.

Thanks.

  • Brian

Hi @lizbethl4,

For total minutes you could do something like this, leverage Group By to identify the sets of rows and use Min and Max values to calculate the total duration in minutes.

Paste the M code below into a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwVNJRMjIyAJJFqenxyfm5BTmpJalgQRCha6FrZGBkiMRUMLCwAssZGhmbmALp0GDXIKVYHbhhhqZQw4pLEotK8JpkbmVkjt0kI5zOMsbtLFMTPIZhOgunSUBnWWCYFAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EVENT_ID = _t, EVENT_TYPE = _t, EVENT_NAME = _t, RECORD_NUMBER = _t, EVENT_DATE = _t, EVENT_TIME = _t, USER_ID = _t, USER_NAME = _t]),
    ChType = Table.TransformColumnTypes( Source, {{"EVENT_DATE", type date}, {"EVENT_TIME", type datetime}}, "en-150"),
    AddTotalMin = 
        Table.ExpandTableColumn(
            Table.Group( ChType, {"EVENT_ID", "RECORD_NUMBER", "USER_ID"}, 
                {
                    { "AllRows", each _, type table [EVENT_ID = nullable text, EVENT_TYPE = nullable text, EVENT_NAME = nullable text, RECORD_NUMBER = nullable text, EVENT_DATE = nullable date, EVENT_TIME = nullable datetime, USER_ID = nullable text, USER_NAME = nullable text] }, 
                    { "Total Minutes", each Duration.TotalMinutes(Duration.From(List.Max([EVENT_TIME]) - List.Min([EVENT_TIME]))), type nullable number }
                }
            )[[AllRows], [Total Minutes]], 
        "AllRows", {"EVENT_ID", "EVENT_TYPE", "EVENT_NAME", "RECORD_NUMBER", "EVENT_DATE", "EVENT_TIME", "USER_ID", "USER_NAME"}, {"EVENT_ID", "EVENT_TYPE", "EVENT_NAME", "RECORD_NUMBER", "EVENT_DATE", "EVENT_TIME", "USER_ID", "USER_NAME"}
    )
in
    AddTotalMin

I hope this is helpful

2 Likes

reg_start_comp.pbix (23.6 KB)

Hi @lizbethl4.

Here’s another way using DAX measures:


Start Time = 
VAR _ID = SELECTEDVALUE( REGINREGOUT[Event ID] )
VAR _StartTime = 
    CALCULATE( MAX( REGINREGOUT[Event Time] ),
        FILTER( REGINREGOUT,
            AND(
                MAX( REGINREGOUT[Event ID] ) = _ID,
                REGINREGOUT[EVENT_NAME] = "reg_start" 
            )
        )
    )
RETURN
_StartTime

Complete Time = 
VAR _ID = SELECTEDVALUE( REGINREGOUT[Event ID] )
VAR _CompleteTime = 
    CALCULATE( MAX( REGINREGOUT[Event Time] ),
        FILTER( REGINREGOUT,
            AND(
                MAX( REGINREGOUT[Event ID] ) = _ID,
                REGINREGOUT[EVENT_NAME] = "reg_complete" 
            )
        )
    )
RETURN
_CompleteTime

Difference in Minutes = 
DATEDIFF( [Start Time], [Complete Time], MINUTE )

Difference in hh:mm = 
VAR _Hours = INT( DIVIDE( [Difference in Minutes], 60, 0 ) )
VAR _Minutes = INT( [Difference in Minutes] - _Hours * 60 )
VAR _DifferenceInHHMM = FORMAT( _Hours, "00" ) & ":" & FORMAT( _Minutes, "00" )
VAR _Result = IF( ISBLANK( [Difference in Minutes] ), BLANK(), _DifferenceInHHMM )

RETURN
_Result

Hope it helps.
Greg
eDNA Forum - Difference in hh_mm.pbix (28.9 KB)

2 Likes

Thanks Greg. I’m trying it right now.

Hi Greg,
When I begin “complete time =” I am getting a red line error for “complete time”
Am I missing some syntax?

I found it in the PBI you sent back. no worries. they are separate key measures

Hi Greg,
I tried it but when I add the measures to my original model, once I add the measures, the values go blank.
image

Hi @lizbethl4.

I used new calculated columns in my demo … I’d expect you can just slightly adjust the measures to suit your implementation. Try to “build-up” the table one column at a time; hopefully the issue will present itself.

If it would help, upload your work-in progress PBIX and I’ll have a look.

Greg

I’m using my original pbi which has sensitive information on it. so the columns i sent originally were named different than in my original PBI. I renamed them appropriately for my implementation. Why you created calculated column?

reg_start_comp.pbix (23.4 KB)

Your EVENT_ID column was text, and your EVENT_DATE and EVENT_TIME columns were both date/time, so I created new columns instead of changing the original.
Greg

Your PBIX doesn’t have the measures from my sample PBIX in them … once they are added and adjusted for column names as necessary, all should be good.
Greg

Hi @lizbethl4 , did the response provided by @Greg help you solve your query?

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.

Hi for some reason it wouldn’t work in my model but it worked in the sample pbi I sent and received from @Greg.

I went a different route and used the Lag windows function and creating a column in PBI to get hhmmss see below:

,LAG(table.EVENT_TIME,1) OVER (PARTITION BY table.EVENT_ID ORDER BY (table.EVENT_ID)) AS ‘PRIORDATETIME’
,table.EVENT_TIME - LAG(table.EVENT_TIME,1) OVER (PARTITION BY table.EVENT_ID ORDER BY (table.EVENT_ID )) AS ‘LAG_TIME_HRS_MIN’

I then created a column in PBI
LAG_MINUTES2 = HOUR(table[LAG_TIME_HRS_MIN])*60 + MINUTE(table[LAG_TIME_HRS_MIN])
to get the hhmmss I needed.

Hi @lizbethl4, is this post considered as solved. If yes, kindly tick the appropriate entry as “Solution” to mark this query as solved. Thanks

Hi @lizbethl4, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, please create a new thread.