Forum Discussion

Bu__'s avatar
Bu__
Frequent Visitor
2 years ago
Solved

Find duration spent between 2 dates in multiple rows

Have a data like below

Ticket numCreated
Ticket_111/3/2022 0:06
Ticket_111/3/2022 0:18
Ticket_111/3/2022 0:21
Ticket_111/3/2022 0:22
Ticket_111/3/2022 0:23
Ticket_111/3/2022 0:24
Ticket_111/3/2022 0:26
Ticket_212/12/2022 13:40
Ticket_212/12/2022 13:41
Ticket_212/12/2022 13:43
Ticket_212/12/2022 13:45
Ticket_212/12/2022 13:45
Ticket_212/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 numCreated Time Taken
Ticket_111/3/2022 0:060.1919444
Ticket_111/3/2022 0:180.0547222
Ticket_111/3/2022 0:210.0175
Ticket_111/3/2022 0:220.0108333
Ticket_111/3/2022 0:230.0166667
Ticket_111/3/2022 0:240.0369444
Ticket_111/3/2022 0:260
Ticket_212/12/2022 13:400.0158333
Ticket_212/12/2022 13:410.0205556
Ticket_212/12/2022 13:430.0419444
Ticket_212/12/2022 13:450.0041667
Ticket_212/12/2022 13:450.0061111
Ticket_212/12/2022 13:46

0

 

@DateTime Functions
@Dax
@Dax help
@NeedHelp
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    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 )

     

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    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 )