Forum Discussion
Reaction-/Resolution time calculations (with a twist)
Impactful IndividualI 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
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.
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?
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.