Forum Discussion
Reaction-/Resolution time calculations (with a twist)
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
13 Replies
- austinsense
Impactful Individual
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
- 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.
