Find time spent in each state using DAX

Question

calculate time spent in each state i have a data as given below:

Ticket num	Created	state
Ticket_1	11/3/2022 0:06	P4
Ticket_1	11/3/2022 0:18	P4
Ticket_1	11/3/2022 0:21	P3
Ticket_1	11/3/2022 0:22	P2
Ticket_1	11/3/2022 0:23	P1
Ticket_1	11/3/2022 0:24	P1
Ticket_1	11/3/2022 0:26	P1
Ticket_2	12/12/2022 13:40	P1
Ticket_2	12/12/2022 13:41	P1
Ticket_2	12/12/2022 13:43	P2
Ticket_2	12/12/2022 13:45	P2
Ticket_2	12/12/2022 13:45	P4
Ticket_2	12/12/2022 13:46	P3
Ticket_2	12/13/2022 14:00	P1

i would now like to calculate time spent by each state in hours/seconds for every ticket in a 100% stacked bar chart and see the transition for state for Ticket_1 which changes from P4-->P3-->P2-->P1

Final chart to look something like this

Answer 1

Add a Calculated Column with:

Duration = 
  var thisTime = [Created]
  var toTime = CALCULATE(MIN([Created]), ALLEXCEPT('Ticket', 'Ticket'[Ticket num]), 'Ticket'[Created] > thisTime)
  return DATEDIFF(thisTime, toTime, MINUTE)

To give you:

Note

• Assuming the first (min) date is the start.
• Your table needs a Closed datetime column otherwise the last row for each ticket won't be included.

Additional
To get the chart to show the order of state, then the best that can be achieved with native visuals is like shown below. However, unable to have the each Px color match.

Create two more calculated columns with the following (in order):

state_change = 
  var thisCreated = [Created]
  var thisTicket = [Ticket num]
  var prevFilter = TOPN(1, FILTER(Ticket, Ticket[Ticket num] = thisTicket && Ticket[Created] < thisCreated), [Created], DESC)
  var prevValue = CALCULATE(MIN('Ticket'[state]), prevFilter)
  return IF(prevValue <> [state], [state])


state_index = 
  var thisTime = [Created]
  var stateCount = CALCULATE(COUNT(Ticket[state_change]), ALLEXCEPT('Ticket', 'Ticket'[Ticket num]), 'Ticket'[Created] <= thisTime)
  return stateCount

Then for the chart:

• Y-axis - Ticket num
• X-axis - Duration
• Legend - state_index
• Tooltip - state
• Properties
  • Switch off the legend (optional)
  • Data labels On.
    Data labels > Values > Custom label On and add state to the field.
    Overflow text On.