Forum Discussion
Duration between two tickets statuses
I have a change log record containing (3) columns:
"TicketNumber", "ChangedWhen", "Status"
Each "TicketNumber" will appear in multiple records.
Each record can have 1 of 6 "Status" and a date and time that the "Status" changed.
The 6 statuses are not used in constant succession so the ticket may trasition from and back to statuses as seen in the example below.
We are trying to calculate the duration in minutes between the first InProcess "ChangedWhen" record and the last Committed "ChangedWhen" record for each "TicketNumber".
Any suggestions?
hi, Anonymous
You could use DATEDIFF Function to create a measure
Measure = var _firstInProcess =CALCULATE(MIN('Table'[ChangedWhen]),'Table'[Status]="In Process") var _lastCommitted=CALCULATE(MAX('Table'[ChangedWhen]),'Table'[Status]="Committed") return DATEDIFF(_firstInProcess,_lastCommitted,MINUTE)Then drag TicketNumber and the measure into a table visual.
Best Regards,
Lin
2 Replies
hi, Anonymous
You could use DATEDIFF Function to create a measure
Measure = var _firstInProcess =CALCULATE(MIN('Table'[ChangedWhen]),'Table'[Status]="In Process") var _lastCommitted=CALCULATE(MAX('Table'[ChangedWhen]),'Table'[Status]="Committed") return DATEDIFF(_firstInProcess,_lastCommitted,MINUTE)Then drag TicketNumber and the measure into a table visual.
Best Regards,
Lin
Hey,
please provide an Excel file with some sample data, upload the file to onedrive or dropbox and share the link.
I wonder if each ticket has also a "committed state"?
What is your expected result if there is no "Committed" state?
Regards,
Tom
