Duration between two tickets statuses

[Anonymous]

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? 

[v-lili6-msft · Accepted solution]

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

[TomMartens]

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