Forum Discussion

fabd's avatar
fabd
Icon for Helper I rankHelper I
4 years ago
Solved

Average day for incident resolution

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 ?
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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.

     

3 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.

     

    • fabd's avatar
      fabd
      Icon for Helper I rankHelper I
      4 years ago

      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?