Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
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
Proud to be a Super User!
@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.
@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?
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |