Forum Discussion

ToddMate's avatar
ToddMate
Helper II
3 years ago
Solved

Calculate Time Difference.

Hi, I need assistance determining the time difference in the example below. Using the same tx_number, and then sorting by tx_date, i am trying to determine the response time from "Customer Emai...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi ToddMate ,

     

    It's eaiser to achieve by creating a calculated column.

     

    time =
VAR _MAX =
    MAXX (
        FILTER ( 'tx_dates', [tx_number] = EARLIER ( tx_dates[tx_number] ) ),
        [tx_date]
    )
VAR _SECOND =
    DATEDIFF ( [tx_date], _MAX, SECOND )
RETURN
    IF (
        [action] = "Customer Email",
        FORMAT ( ROUNDDOWN ( DIVIDE ( _SECOND, 3600 ), 0 ), "00" ) & ":"
            & FORMAT ( ROUNDDOWN ( DIVIDE ( MOD ( _SECOND, 3600 ), 60 ), 0 ), "00" ) & ":"
            & FORMAT ( MOD ( _SECOND, 60 ), "00" )
    )

     

     

     

     

    Best Regards,

    Stephen Tao

     

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

ryan_mayu's avatar
ryan_mayu
Super User
3 years ago

ToddMate 

pls note that the time column will be TEXT type and can't do the futher calculation

Column = 
VAR _response=minx(FILTER('Table','Table'[tx_number]=EARLIER('Table'[tx_number])&&'Table'[action]="Our Response" &&'Table'[tx_date]>EARLIER('Table'[tx_date])),'Table'[tx_date])
VAR _datedif=DATEDIFF('Table'[tx_date],_response,SECOND)    
VAR _h=int(_datedif/3600)
VAR _m=right("0"&int((_datedif-_h*3600)/60),2)
VAR _s=right("0"&(_datedif-_h*3600-_m*60),2)
return if('Table'[action]="Customer Email",_h&":"&_m&":"&_s)

pls see the attachment below