Average day for incident resolution

[fabd]

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))

It works fine. I get, for all incident, the delai in second for resolution.

But i would like to have a single KPI Metric, which will says :

2 days, 3 hours, 5 min

I have no idea how to deal with.

Is there someone who can drive me ?

[v-kkf-msft · Accepted 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.

[coskuersanli]

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

[fabd]

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?