Forum Discussion
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 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 |
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.
5 Replies
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/339586.
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 - __PreviousGreg_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.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?
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
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.
