Forum Discussion

Matic20's avatar
Matic20
Frequent Visitor
7 years ago
Solved

Date and Time Difference

Hello  team 

I have there Columns, Status ( this includes things like pause, resume, close, create, postreply), TicketID(primary key ) and Log Status Creation( time for every status). I need to calculate forexample the Date $ time difference btn postreply and closeticket.

Please Advice 

Ticket IDLOG CREATION DATESTAFFEVENT NAME
165887/25/2019 19:22Emily  Spotpostinternalnote
165887/25/2019 19:22Emily  Spotcloseticket
165887/25/2019 19:22Emily  Spotpostinternalnote
165887/25/2019 19:22Emily  Spotpostreply
165887/25/2019 19:22Emily  Spotpostinternalnote
165887/25/2019 19:21Emily  Spotpostinternalnote
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi Matic20 ,

    For your requirement, you could create the measure below.

    difference =
VAR pauseticket =
    CALCULATE (
        MAX ( 'Table'[LOG CREATION DATE] ),
        FILTER ( 'Table', 'Table'[EVENT NAME] = "pauseticket" )
    )
VAR resumeticket =
    CALCULATE (
        MAX ( 'Table'[LOG CREATION DATE] ),
        FILTER ( 'Table', 'Table'[EVENT NAME] = "resumeticket" )
    )
VAR diff =
    DATEDIFF ( pauseticket, resumeticket, SECOND )
VAR hours =
    INT ( diff / 3600 )
VAR minutes =
    INT ( MOD ( diff - ( hours * 3600 ), 3600 ) / 60 )
RETURN
    hours & "hr" & minutes & "mints"

    Here is the output.

    Hope this can help you.

    Best  Regards,

    Cherry

     

5 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi Matic20 ,

    Normally, we could use DATEIFF function to calculate the datetime difference.

    I still have a little confused about your scenario.

    If it is convenient, could you share your desired output so that we could help further on it.

    Best  Regards,

    Cherry

     

    • Matic20's avatar
      Matic20
      Frequent Visitor
      7 years ago
      v-piga-msft wrote:

      Hi Matic20 ,

      Normally, we could use DATEIFF function to calculate the datetime difference.

      I still have a little confused about your scenario.

      If it is convenient, could you share your desired output so that we could help further on it.

      Best  Regards,

      Cherry

       

      Difference btn the eventname pauseticket and resumeticket (7/26/2019 18:49) and (7/26/2019 14:44) 

      Result = 4hr and 5 mints

      LOG CREATION DATEEVENT NAMETICKET ID
      7/26/2019 19:10postinternalnote16639
      7/26/2019 19:10closeticket16639
      7/26/2019 19:10postreply16639
      7/26/2019 18:49resumeticket16639
      7/26/2019 18:26postreply16639
      7/26/2019 14:44pauseticket16639
      7/26/2019 14:44postreply16639
      7/26/2019 14:44claimticket16639
      7/26/2019 14:34postinternalnote16639
      7/26/2019 14:34overdueticket16639
      7/26/2019 14:34postinternalnote16639
      7/26/2019 14:34postinternalnote16639
      7/26/2019 14:33postreply16639
      7/26/2019 14:33createticket16639

       

       

      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        7 years ago

        Hi Matic20 ,

        For your requirement, you could create the measure below.

        difference =
VAR pauseticket =
    CALCULATE (
        MAX ( 'Table'[LOG CREATION DATE] ),
        FILTER ( 'Table', 'Table'[EVENT NAME] = "pauseticket" )
    )
VAR resumeticket =
    CALCULATE (
        MAX ( 'Table'[LOG CREATION DATE] ),
        FILTER ( 'Table', 'Table'[EVENT NAME] = "resumeticket" )
    )
VAR diff =
    DATEDIFF ( pauseticket, resumeticket, SECOND )
VAR hours =
    INT ( diff / 3600 )
VAR minutes =
    INT ( MOD ( diff - ( hours * 3600 ), 3600 ) / 60 )
RETURN
    hours & "hr" & minutes & "mints"

        Here is the output.

        Hope this can help you.

        Best  Regards,

        Cherry