Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Rakshana
Frequent Visitor

Need help with status change of tickets over time

Hi, 

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

Rakshana_0-1673196334253.png

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. 

Rakshana_2-1673197115833.png

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.

1 ACCEPTED SOLUTION
Greg_Deckler
Community Champion
Community Champion

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Community Champion
Community Champion

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Thanks a lot @Greg_Deckler! This was helpful 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.