Calculate Time Difference.

[ToddMate]

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 Email" to "Our Response".

In the scenario below, only the middle 3 "Customer Email" entries would have a calculation.

The calculation would be the first "Our Response" (date) AFTER the "Customer Email" and would simply be "Our Response" (date) minus "Customer Email" (date) to show as below (in red)

If there is no "Our Response" after the "Customer Email" then the value would be null.

Can this be done in Power Query AND also via Calculated Columns. Ideally i would like to have this action completed in Power Query.

table_name: tx_dates

tx_number	tx_category	tx_date	action	time
102435	143	24/05/2022 15:23:00	Our Response
102435	143	24/05/2022 15:28:00	Customer Email	31:00:00
102435	143	24/05/2022 17:32:00	Customer Email	28:56:00
102435	143	25/05/2022 19:54:00	Customer Email	2:34:00
102435	143	25/05/2022 22:28:00	Our Response

[v-stephen-msft · Accepted solution]

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]

@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

[ToddMate]

@Greg_Deckler ,

I'm not sure if i have missed something in this exercise, but as i only have one date per row and i am looking up date values below if x criteria is met, the article you provided doesn't appear to provide a solution.

Can you please review and see if i have missed something.

[Greg_Deckler]

@ToddMate It seemed like you were trying to take a date/time from the current and subtract it from the date/time that appears in a previous row. Is that correct?

[Greg_Deckler]

@ToddMate See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous