Calculating time difference between two entries based on Name

[gwstew01]

I am attempting to calculate the time between user entries based on a sample dataset below. Everything I have tried returns a blank when the previous user is different.  I would like to create a calculated column that states the time in minutes from the users last entry. Thank you in advance, any help would be greatly appreciated. 

ID	Start time	Name	Serial
1	1/4/2023 6:07	Joe	FA2959059
2	1/4/2023 6:10	Bob	EA1718776
3	1/4/2023 6:11	Joe	EZ7876073
4	1/4/2023 6:17	Bob	FA8496490
5	1/4/2023 6:00	Tom	IO6534201
6	1/4/2023 6:26	Tom	DX5874653
7	1/4/2023 6:26	Tom	FA7756353
8	1/4/2023 6:29	Joe	EC3715128
9	1/4/2023 6:22	Bob	DP5017825
10	1/4/2023 6:35	Bob	DQ7629157
11	1/4/2023 6:26	Tom	IU0097619
12	1/4/2023 6:46	Tom	IO1884803
13	1/4/2023 6:46	Tom	FB6047252
14	1/4/2023 6:49	Joe	DP5933893
15	1/4/2023 6:51	Bob	DQ7257435
16	1/4/2023 6:57	Joe	DQ7543159
17	1/4/2023 7:03	Joe	FA7563565
18	1/4/2023 7:01	Bob	DP5079926
19	1/4/2023 6:46	Tom	II0894636
20	1/4/2023 7:15	Tom	EH0771377
21	1/4/2023 7:15	Tom	FA6843160
22	1/4/2023 7:18	Joe	DQ7162684
23	1/4/2023 7:10	Bob	DQ7247192
24	1/4/2023 7:21	Bob	DP5842204
25	1/4/2023 7:27	Bob	DP5071737
26	1/4/2023 7:26	Joe	EA1488343
27	1/4/2023 7:35	Joe	II0947655
28	1/4/2023 7:38	Bob	DM4342578
29	1/4/2023 7:55	Joe	FA2972328
30	1/4/2023 7:15	Tom	CY9930105
31	1/4/2023 8:10	Tom	DP5841874
32	1/4/2023 8:10	Tom	DQ7692967
33	1/4/2023 8:04	Joe	DP5860356
34	1/4/2023 8:09	Bob	DP4964209
35	1/4/2023 8:17	Joe	DP5606351
36	1/4/2023 8:14	Bob	DP5779962
37	1/4/2023 8:26	Bob	DP5775000
38	1/4/2023 8:17	Joe	DQ7567956
39	1/4/2023 8:35	Bob	DQ5960622
40	1/4/2023 8:35	Bob	DQ5960622
41	1/4/2023 8:32	Joe	DP5942894
42	1/4/2023 8:41	Bob	DM4152731
43	1/4/2023 8:39	Joe	DP5983077
44	1/4/2023 8:47	Joe	FA7300814
45	1/4/2023 8:47	Bob	DP6063608
46	1/4/2023 9:00	Bob	978447576
47	1/4/2023 9:02	Bob	978447576
48	1/4/2023 8:57	Joe	EJ5744252
49	1/4/2023 9:11	Bob	DR7238885
50	1/4/2023 9:16	Joe	DR6832921

[Anonymous · Accepted solution]

Hi @gwstew01 ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR _min =
    CALCULATE (
        MAX ( 'Table'[ID] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ID] < SELECTEDVALUE ( 'Table'[ID] )
                && 'Table'[Name] = SELECTEDVALUE ( 'Table'[Name] )
        )
    )
VAR _1 =
    CALCULATE (
        MAX ( 'Table'[Start time] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ID] = _min )
    )
VAR _result =
    MINUTE ( MAX ( 'Table'[Start time] ) - _1 )
VAR _minid =
    CALCULATE (
        MIN ( 'Table'[ID] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Name] = SELECTEDVALUE ( 'Table'[Name] ) )
    )
RETURN
    IF ( MAX ( 'Table'[ID] ) = _minid, BLANK (), _result )

Best Regards
Community Support Team _ Polly

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

[gwstew01]

@Ashish_Mathur @Anonymous both of your most recent solutions work, thank you both so much!

[Ashish_Mathur]

You are welcome.

[gwstew01]

@Anonymous thank you for your reply, but it doesnt look like the provided solution works. What I am looking for is the below. For example: Joe's first entry was at 6:07 (ID=1) his next entry was at 6:11 (ID=3), I am trying to calculate the difference between these two times and get the result of 4 minutes. His next entry was at 6:29 (ID=8), so the result should be 18 minutes (6:11 to 6:29 is 18 minutes elapsed time). Hopefully this helps. 

ID	Start time	Name	Serial	Time in minutes
1	1/4/2023 6:07	Joe	FA2959059
2	1/4/2023 6:10	Bob	EA1718776
3	1/4/2023 6:11	Joe	EZ7876073	4
4	1/4/2023 6:17	Bob	FA8496490	7
5	1/4/2023 6:00	Tom	IO6534201
6	1/4/2023 6:26	Tom	DX5874653	26
7	1/4/2023 6:26	Tom	FA7756353	0
8	1/4/2023 6:29	Joe	EC3715128	18
9	1/4/2023 6:22	Bob	DP5017825	5
10	1/4/2023 6:35	Bob	DQ7629157	13

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula should work

=Data[Start time]-COALESCE(CALCULATE(MAX(Data[Start time]),FILTER(Data,Data[name]=EARLIER(Data[name])&&Data[Start time]<EARLIER(Data[Start time]))),Data[Start time])

[Anonymous · Accepted solution]

Hi @gwstew01 ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR _min =
    CALCULATE (
        MAX ( 'Table'[ID] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ID] < SELECTEDVALUE ( 'Table'[ID] )
                && 'Table'[Name] = SELECTEDVALUE ( 'Table'[Name] )
        )
    )
VAR _1 =
    CALCULATE (
        MAX ( 'Table'[Start time] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ID] = _min )
    )
VAR _result =
    MINUTE ( MAX ( 'Table'[Start time] ) - _1 )
VAR _minid =
    CALCULATE (
        MIN ( 'Table'[ID] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Name] = SELECTEDVALUE ( 'Table'[Name] ) )
    )
RETURN
    IF ( MAX ( 'Table'[ID] ) = _minid, BLANK (), _result )

Best Regards
Community Support Team _ Polly

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

[Anonymous]

Hi @gwstew01 ,

I have created a simple sample, please refer to it to see if it helps you.

Create a measure.

Measure = var _maxdate=CALCULATE(MAX('Table'[start time]),FILTER(ALL('Table'),'Table'[name]=SELECTEDVALUE('Table'[name])))
return
_maxdate-MAX('Table'[start time])

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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