Need help with status change of tickets over time

[Rakshana]

Hi, 

I have data in the following format. A ticket moves across multiple stages over time. Data is collected monthly.

The status order is Submitted -> Screening -> Approved -> Completed

I want to track the flow of tickets. Eg: 30 tickets submitted in Jan -> 15 of which moved into screening in Feb -> 7 approved in March -> 4 completed in April. There might be new submissions every month as well. 

I am able to get a bar chart that shows the count of tickets in each state per month. 

But how do I show the number of tickets that have changed from the previous month to now in a visual? For example how many moved from submitted to screening from Jan to March? or how many tickets submitted in Jan have been completed by June. 

Any suggestions/ guidance is appreciated. Thank you.

[Greg_Deckler · Accepted solution]

@Rakshana For the first question, you could do something like this:

Number of Changed Tickets PM =
  VAR __Tickets = SELECTCOLUMNS('Table',"__TicketID",[TicketID],"__Status",[Status])
  VAR __PMEOM = EOMONTH(MAX('Table'[DateAsOf]),-1)
  VAR __PMBOM = DATE(YEAR(__PMEOM), MONTH(__PMEOM), 1)
  VAR __TicketsPM = SELECTCOLUMNS(FILTER(ALL('Table'),[DateAsOf] >= __PMBOM && [DateAsOf] <= __PMEOM),"__TicketID",[TicketID],"__Status",[Status])
  VAR __Changed = EXCEPT(__Tickets, __TicketsPM)
  VAR __Result = COUNTROWS(__Changed)
RETURN
  __Result

[Rakshana]

Thanks a lot @Greg_Deckler! This was helpful