Find duration spent between 2 dates in multiple rows

[Bu__]

Have a data like below

Ticket num	Created
Ticket_1	11/3/2022 0:06
Ticket_1	11/3/2022 0:18
Ticket_1	11/3/2022 0:21
Ticket_1	11/3/2022 0:22
Ticket_1	11/3/2022 0:23
Ticket_1	11/3/2022 0:24
Ticket_1	11/3/2022 0:26
Ticket_2	12/12/2022 13:40
Ticket_2	12/12/2022 13:41
Ticket_2	12/12/2022 13:43
Ticket_2	12/12/2022 13:45
Ticket_2	12/12/2022 13:45
Ticket_2	12/12/2022 13:46

Would like to calculate Time Taken in each ticket by subtracting the created column data for each row for all ticket num.

Here Time taken for 1st row calculated as ( 11/3/2022 0:18 - 11/3/2022 0:06) *24 = 0.1919444 ....until another ticket starts, to have 0 before a new ticket starts

Results to look something like below table

Ticket num	Created	Time Taken
Ticket_1	11/3/2022 0:06	0.1919444
Ticket_1	11/3/2022 0:18	0.0547222
Ticket_1	11/3/2022 0:21	0.0175
Ticket_1	11/3/2022 0:22	0.0108333
Ticket_1	11/3/2022 0:23	0.0166667
Ticket_1	11/3/2022 0:24	0.0369444
Ticket_1	11/3/2022 0:26	0
Ticket_2	12/12/2022 13:40	0.0158333
Ticket_2	12/12/2022 13:41	0.0205556
Ticket_2	12/12/2022 13:43	0.0419444
Ticket_2	12/12/2022 13:45	0.0041667
Ticket_2	12/12/2022 13:45	0.0061111
Ticket_2	12/12/2022 13:46	0

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understodd your question correctly, but I tried to create a sample pbix file like below.

It is for creating a new column, and the result is described in Second.

Please check the below picture and the attached pbix file.

OFFSET function (DAX) - DAX | Microsoft Learn

COALESCE function (DAX) - DAX | Microsoft Learn

expected reuslt CC = 
VAR _currentdatetime = Data[Created]
VAR _nextdatetime =
    MAXX (
        OFFSET (
            1,
            SUMMARIZE ( Data, Data[Ticket num], Data[Created] ),
            ORDERBY ( Data[Created], ASC ),
            ,
            PARTITIONBY ( Data[Ticket num] )
        ),
        Data[Created]
    )
RETURN
    COALESCE ( DATEDIFF ( _currentdatetime, _nextdatetime, SECOND ), 0 )