Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I haven't practice for several years.. I must admit that i lost everything, and i am unable to do what i need.
So, what i want ?
My boss give me a new responsability : KPI for my team ! 😞
I create a dataset with some Service Now query to get the state of incident and i would like to know in how many time incident are solved.
To do this, i have 2 inputs :
- date open
- date close
and i create a new column called duration
Duree = SUMX(incident_STI_indus, DATEDIFF(incident_STI_indus[date_creation],incident_STI_indus[date_fermeture],SECOND))
Solved! Go to Solution.
Hi @fabd ,
Please create the new column:
Duree =
DATEDIFF (
incident_STI_indus[date_creation],
incident_STI_indus[date_fermeture],
MINUTE
)
Then create the measure:
Measure =
VAR AveDur =
AVERAGE ( incident_STI_indus[Duree] )
VAR Days =
INT ( AveDur / 60 / 24 )
VAR Hours =
INT ( AveDur / 60 - Days * 24 )
VAR Minutes =
ROUND ( MOD ( AveDur, 60 ), 0 )
VAR DaysText =
IF ( Days >= 1, FORMAT ( Days, "General Number" ) & " days" )
VAR HoursText =
FORMAT ( Hours, "General Number" ) & " hours"
VAR MinutesText =
FORMAT ( Minutes, "General Number" ) & " min"
RETURN
COMBINEVALUES ( ", ", DaysText, HoursText, MinutesText )
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @fabd ,
Please create the new column:
Duree =
DATEDIFF (
incident_STI_indus[date_creation],
incident_STI_indus[date_fermeture],
MINUTE
)
Then create the measure:
Measure =
VAR AveDur =
AVERAGE ( incident_STI_indus[Duree] )
VAR Days =
INT ( AveDur / 60 / 24 )
VAR Hours =
INT ( AveDur / 60 - Days * 24 )
VAR Minutes =
ROUND ( MOD ( AveDur, 60 ), 0 )
VAR DaysText =
IF ( Days >= 1, FORMAT ( Days, "General Number" ) & " days" )
VAR HoursText =
FORMAT ( Hours, "General Number" ) & " hours"
VAR MinutesText =
FORMAT ( Minutes, "General Number" ) & " min"
RETURN
COMBINEVALUES ( ", ", DaysText, HoursText, MinutesText )
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi fabd,
Can you check below link, hope it helps.
https://community.powerbi.com/t5/Desktop/Calculate-time-in-Days-Hours-amp-Minutes/td-p/406017
Hi
thanks ! great !
i have now the duration in days, hours, min for each incident
How i could make an average of the whole column?
User | Count |
---|---|
98 | |
91 | |
84 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |