The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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 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