Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
First post - please be kind 🙂
Using PowerBi to pull common Incident and Task Ticket information from Service Now and combine via a Union query.
I'm looking to show the Average Incident Resolve Time and the Average Task resolve time.
I'm using DATEDIFF as follows ...
DifferenceSECs = DATEDIFF(ServiceNow[opened_at],ServiceNow[resolved_at],SECOND)
opened_at = 17/07/2017 15:57:40
resolved_at = 18/07/2017 10:24:00
DifferenceSECs = 66380
I'm then taking that result in seconds and converting it to a duration
New Duration = format(((ServiceNow[DifferenceSECs] / 60)/60)/24, "dd:HH:mm:ss")
New Duration = 30:18:26:20
My problem is why has the result got 30 days added at the start.
I'm also getting examples where days are being removed.
opened_at = 18/07/2017 10:39:14
resolved_at = 20/07/2017 13:09:44
DifferenceSECs = 181830
New Duration = 01:02:30:30 - Should be 02:02:30:30
Once I have the correct New Duration, I'm then looking to get the average for which I'm using ..
FORMAT(AVERAGEA(ServiceNow[DifferenceSECs]),"DD:HH:MM: SS")
Apologies for the long post, but is anyone able to advise where I'm going wrong.
Thanks in advance
Rob
Solved! Go to Solution.
Hi @rg72,
Please try below formulas:
DifferenceSECs = DATEDIFF ( ServiceNow[opened_at], ServiceNow[resolved_at], SECOND ) Duration = RIGHT ( "0" & INT ( ServiceNow[DifferenceSECs] / ( 24 * 60 * 60 ) ), 2 ) & ":" & RIGHT ( "0" & INT ( ( ServiceNow[DifferenceSECs] - INT ( ServiceNow[DifferenceSECs] / ( 24 * 60 * 60 ) ) * ( 24 * 60 * 60 ) ) / 3600 ), 2 ) & ":" & RIGHT ( "0" & INT ( ( ServiceNow[DifferenceSECs] - INT ( ServiceNow[DifferenceSECs] / 3600 ) * 3600 ) / 60 ), 2 ) & ":" & RIGHT ( "0" & ( ServiceNow[DifferenceSECs] - INT ( ServiceNow[DifferenceSECs] / 60 ) * 60 ), 2 ) Duration Average = RIGHT ( "0" & INT ( AVERAGEA ( ServiceNow[DifferenceSECs] ) / ( 24 * 60 * 60 ) ), 2 ) & ":" & RIGHT ( "0" & INT ( ( AVERAGEA ( ServiceNow[DifferenceSECs] ) - INT ( AVERAGEA ( ServiceNow[DifferenceSECs] ) / ( 24 * 60 * 60 ) ) * ( 24 * 60 * 60 ) ) / 3600 ), 2 ) & ":" & RIGHT ( "0" & INT ( ( AVERAGEA ( ServiceNow[DifferenceSECs] ) - INT ( AVERAGEA ( ServiceNow[DifferenceSECs] ) / 3600 ) * 3600 ) / 60 ), 2 ) & ":" & RIGHT ( "0" & ( AVERAGEA ( ServiceNow[DifferenceSECs] ) - INT ( AVERAGEA ( ServiceNow[DifferenceSECs] ) / 60 ) * 60 ), 2 )
Best regards,
Yuliana Gu
Hi @rg72,
Please try below formulas:
DifferenceSECs = DATEDIFF ( ServiceNow[opened_at], ServiceNow[resolved_at], SECOND ) Duration = RIGHT ( "0" & INT ( ServiceNow[DifferenceSECs] / ( 24 * 60 * 60 ) ), 2 ) & ":" & RIGHT ( "0" & INT ( ( ServiceNow[DifferenceSECs] - INT ( ServiceNow[DifferenceSECs] / ( 24 * 60 * 60 ) ) * ( 24 * 60 * 60 ) ) / 3600 ), 2 ) & ":" & RIGHT ( "0" & INT ( ( ServiceNow[DifferenceSECs] - INT ( ServiceNow[DifferenceSECs] / 3600 ) * 3600 ) / 60 ), 2 ) & ":" & RIGHT ( "0" & ( ServiceNow[DifferenceSECs] - INT ( ServiceNow[DifferenceSECs] / 60 ) * 60 ), 2 ) Duration Average = RIGHT ( "0" & INT ( AVERAGEA ( ServiceNow[DifferenceSECs] ) / ( 24 * 60 * 60 ) ), 2 ) & ":" & RIGHT ( "0" & INT ( ( AVERAGEA ( ServiceNow[DifferenceSECs] ) - INT ( AVERAGEA ( ServiceNow[DifferenceSECs] ) / ( 24 * 60 * 60 ) ) * ( 24 * 60 * 60 ) ) / 3600 ), 2 ) & ":" & RIGHT ( "0" & INT ( ( AVERAGEA ( ServiceNow[DifferenceSECs] ) - INT ( AVERAGEA ( ServiceNow[DifferenceSECs] ) / 3600 ) * 3600 ) / 60 ), 2 ) & ":" & RIGHT ( "0" & ( AVERAGEA ( ServiceNow[DifferenceSECs] ) - INT ( AVERAGEA ( ServiceNow[DifferenceSECs] ) / 60 ) * 60 ), 2 )
Best regards,
Yuliana Gu
Many thanks - Works a treat 🙂
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |