Reaction-/Resolution time calculations (with a twist)

[Anonymous]

Hi,

I'm trying to get reaction-/resolutiontime calculations from data from our ticket system formed like this:

Ticket ID	LineId	Ticket created	Ticket modified
38	72	2010-06-02 20:36:05	2010-06-02 20:36:05
38	199	2010-06-02 20:36:05	2010-06-18 12:02:26
39	73	2010-06-02 21:17:22	2010-06-02 21:17:22
39	200	2010-06-02 21:17:22	2010-06-18 12:04:11
40	74	2010-06-02 21:43:43	2010-06-02 21:43:43
40	75	2010-06-02 21:43:43	2010-06-02 21:47:39

So everytime some modification is done to a ticket a new line is created with "ticket modified" stamp. Ticket ID stays the same. LineId is unigue.

Reaction time

The time from ticket creation to first ticket modified timestamp

Resolution time

The time from Ticket Creation to last ticket modified timestamp.

Any advice on this would be much appreciated. Thank you!

Br,

Vmu

[austinsense]

I love this, here's how i would approach it.

We want to go through each ticket one at a time (hence we use SUMX with the VALUES function to iterate over each distinct ticket).  Then we need to grab different min/max dates and compare them.

Reaction Time = SUMX(VALUES(tablename[Ticket ID]),
                     CALCULATE(MIN(Ticket modified) - MIN(Ticket created))
                )

Resolution Time = SUMX(VALUES(tablename[Ticket ID]),
                       CALCULATE(MAX(Ticket modified) - MIN(Ticket created))
                  )

Hope this helps

[Anonymous]

Thank you for your reply. I tried your solution but as a measure it doesn't seem to collect any data (show blank). The function doesn't give any errors. I'm looking into sumx function etc. to understand your solution better but meanwhile any additional insight would be appreciated.

Cheers,

Vesa

[austinsense]

Just want to check that 1) you're making a measure and not a calculated column, 2) your date columns are set as dates, & 3) don't use tablename use the actual name of whatever table you have in your model. In your report you should insert the ticket id and the measure we just created.  Also if you copied in the measure, try typing it in instead to make sure all the pieces align to your model.

[Anonymous]

1: Yes I'm creating measure. Actually also tested as calculated column which will result in circular dependencies error.

2: Columns are set as date/time in source.

3: Also using actual names for sure as otherwise function would return an error which it doesn't now

I tested further and if I create a table in my report with ID and these measures as a value Im getting following data:

ID  -       Reaction Time     -  Resolution Time

38  -  30.12.1899 0:00:00   -   14.1.1900 15:26:21

39   -  30.12.1899 0:00:00  -  14.1.1900 14:46:49

.

.

So it is calculating something but gives weird results.

Any ideas?

[Anonymous]

Figured out something!

The first modified timestamp is always the same as ticket creation date. So the function always give 0 seconds for reaction time. The timestamp which is the same as "ticket created" timestamp needs to be ignored in function.

[Anonymous]

Would datediff work in this scenario? I have used it before but that modified timestamp and distincting ticket ID:s gives me headache.

[Vvelarde]

@Anonymous

Hi, try with these measures.

ReactionTime =
DATEDIFF (
    MIN ( Table1[Ticket created] ),
    CALCULATE (
        MIN ( Table1[Ticket modified] ),
        FILTER ( Table1, Table1[Ticket modified] <> MIN ( Table1[Ticket created] ) )
    ),
    HOUR
)

ResolutionTime =
DATEDIFF (
    MIN ( Table1[Ticket created] ),
    CALCULATE (
        MAX ( Table1[Ticket modified] ),
        FILTER ( Table1, Table1[Ticket modified] <> MIN ( Table1[Ticket created] ) )
    ),
    HOUR
)

[Anonymous]

This works! Any hints on how I can get output in Days and hours instead of full hours?

Thank you very much both for contributing!

[Anonymous]

I'm experimenting with formula found here on forum. I changed @austinsense measure to give seconds but with my Resolution-Display-measure function is calculating only full days. Any help on this? My data looks like following:

Created and Modified columns are correct type:

Reaction/Resolution -measures use following function:

Reactionmeasure-seconds =
DATEDIFF (
    MIN ( Tickets[Created] );
    CALCULATE (
        MIN ( Tickets[Muokattu] );
        FILTER ( Tickets; Tickets[Muokattu] <> MIN ( Tickets[Created] ) )
    );
    SECOND
)

------

Resolutionmeasure-seconds =
DATEDIFF (
    MIN ( Tickets[Created] );
    CALCULATE (
        MAX ( Tickets[Muokattu] );
        FILTER ( Tickets; Tickets[Muokattu] <> MIN ( Tickets[Created] ) )
    );
    SECOND
)

Resolution-Display =
FORMAT (
    INT (
        IF (
            MOD ( [Resolutionmeasure-seconds]; 60 ) = 60,0;
            MOD ( [Resolutionmeasure-seconds]; 60 )
        )
            + IF (
                MOD ( INT ( [Resolutionmeasure-seconds] / 60 ); 60 )
                    = 60,0;
                MOD ( INT ( [Resolutionmeasure-seconds] / 60 ); 60 )
                    * 100
            )
            + INT ( [Resolutionmeasure-seconds] / 3600 )
                * 10000
    );
    "0:00:00"
)

[Anonymous]

Little error that I notice myself in my last post. Currently the Resolution-Display shows 375:00:00. We can see that it shows full hours as days.

1351581 seconds / 60

approx 22526,35 minutes / 60

approx 375,44 Hours /24

approx 15,64 Days

[Anonymous]

Getting forward!

Now I'm getting all the data with solution by Jon Gallant but the formula displays HH:MM:SS. If someone could help converting this to DD:HH:MM and we would be all set 🙂

Resolution-Display2 has a following function:

Resolution-Display2 =

// Duration formatting 
// * @konstatinos 1/25/2016
// * Given a number of seconds. returns a format of "hh:mm:ss"
//
// We start with a duration in number of seconds
VAR Duration = [Resolutionmeasure-seconds] // There are 3.600 seconds in an hour
VAR Hours =
    INT ( Duration / 3600 ) // There are 60 seconds in a minute
VAR Minutes =
    INT ( MOD ( Duration - ( Hours * 3600 ); 3600 ) / 60 ) // Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 
VAR Seconds =
    ROUNDUP ( MOD ( MOD ( Duration - ( Hours * 3600 ); 3600 ); 60 ); 0 ) // We round up here to get a whole number
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR H =
    IF ( LEN ( Hours ) = 1; CONCATENATE ( "0"; Hours ); CONCATENATE ( ""; Hours ) ) // Minutes with leading zeros
VAR M =
    IF (
        LEN ( Minutes ) = 1;
        CONCATENATE ( "0"; Minutes );
        CONCATENATE ( ""; Minutes )
    ) // Seconds with leading zeros
VAR S =
    IF (
        LEN ( Seconds ) = 1;
        CONCATENATE ( "0"; Seconds );
        CONCATENATE ( ""; Seconds )
    ) // Now return hours. minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
    CONCATENATE (
        H;
        CONCATENATE ( ":"; CONCATENATE ( M; CONCATENATE ( ":"; S ) ) )
    )

[Anonymous]

And I got it to work. I'm just dismissing the seconds here. No need to do Roundup to minutes as it is close enough. If someone want's make the function smarter (shorter) please do but this works for me.

Resolution-Display3 =

VAR Duration = [Resolutionmeasure-seconds] // 86400 seconds in a day
VAR Days =
    INT ( Duration / 86400 ) //  3600 seconds in a hour
VAR Hours =
    INT ( MOD ( Duration - ( days * 3600 ); 3600 ) / 60 ) // There are 60 seconds in a minute
VAR Minutes =
    INT ( MOD ( Duration - ( Hours * 3600 ); 3600 ) / 60 ) // Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours
VAR Seconds =
    ROUNDUP ( MOD ( MOD ( Duration - ( Hours * 3600 ); 3600 ); 60 ); 0 ) // We round up here to get a whole number
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR D =
    IF ( LEN ( Days ) = 1; CONCATENATE ( "0"; Days ); CONCATENATE ( ""; Days ) )
VAR H =
    IF ( LEN ( Hours ) = 1; CONCATENATE ( "0"; Hours ); CONCATENATE ( ""; Hours ) ) // Minutes with leading zeros
VAR M =
    IF (
        LEN ( Minutes ) = 1;
        CONCATENATE ( "0"; Minutes );
        CONCATENATE ( ""; Minutes )
    ) // Seconds with leading zeros
VAR S =
    IF (
        LEN ( Seconds ) = 1;
        CONCATENATE ( "0"; Seconds );
        CONCATENATE ( ""; Seconds )
    ) // Now return hours. minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
    CONCATENATE (
        D;
        CONCATENATE ( ":"; CONCATENATE ( H; CONCATENATE ( ":"; M ) ) )
    )

[Anonymous]

I had to take a step back. None of the previous solutions give the correct timestamps. I now have a workin logic and functions (almost)

I used calculated COLUMNwhich will find the latest modification date from Modified -column for every ID for RESOLUTION time.

For REACTION time we use the same method but look for earliest timestamp.

Resolution-Timestamp =
  CALCULATE(
    MAX(
      'Tukipyynnot'[Muokattu]);
      FILTER('Tukipyynnot';'Tukipyynnot'[ID] = EARLIER('Tukipyynnot'[ID])
   )
  )

As you can see we now have a column with correct information on every row and can easily take the correct data out with filters. Works great with resolution time and with minimal code.

With response however I still have one obstacle. The earliest timestamp is the same as ticket creation (column "luotu") date. I need to leave that out from calculation so I can get the first timestamp with actions made to new ticket. How can I achieve this in function?

Reaction-Timestamp =
CALCULATE(
    MIN(
      'Tukipyynnot'[Muokattu]);
      FILTER('Tukipyynnot';'Tukipyynnot'[ID] = EARLIER('Tukipyynnot'[ID])
   )
  )

Thank you for any possible input.