Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am trying to show a backlog of total open ticket items that shows the total items open up to each day shown on a line chart. The data I have continains records for both tickets completed and tickets currently in queue. It looks like this:
ticket_id | ticket_start_queue | ticket_end_queue |
1 | 7/23/2018 | |
2 | 7/23/2018 | 7/26/2018 |
3 | 7/25/2018 | 7/27/2018 |
4 | 7/26/2018 | |
5 | 7/26/2018 | 7/29/2018 |
6 | 7/26/2018 | 7/30/2018 |
7 | 7/27/2018 | 7/30/2018 |
8 | 7/29/2018 | 7/30/2018 |
9 | 7/29/2018 | |
10 | 7/29/2018 | 7/31/2018 |
Tickets that have a blank ticket_end_queue are currently still in queue. Currently based on the data above my line chart shows that a snapshot of the open tickets contains 3 open tickets: one for 7/23, one for 7/26, and one for 7/29. What I want to show if possible is the continuous dates and their respective running sum every day for the "open tickets". I'm guessing this would be flagged by saying if ticket_end_queue is blank or if ticket_end_queue > "date on the line chart" then it equals an open ticket. For example, on 7/27/2018 there would be 5 open tickets because:
So based on the data above, the line chart (represented here in a matrix) would look like this:
Date | Open Tickets |
7/23/2018 | 2 |
7/24/2018 | 2 |
7/25/2018 | 3 |
7/26/2018 | 5 |
7/27/2018 | 5 |
7/28/2018 | 5 |
7/29/2018 | 7 |
7/30/2018 | 4 |
7/31/2018 | 3 |
Is that enough info? any idea on how I can accomplish this?
Thanks,
Matt
Solved! Go to Solution.
Hi, Try with this:
1: Create a CalendarTable. Dont related to ticket table.
2. Create a measure
OpenTickets = VAR SelectedDate = SELECTEDVALUE ( 'Calendar'[Date] ) RETURN CALCULATE ( COUNT ( TicketsTable[ticket_id] ), FILTER ( TicketsTable, TicketsTable[ticket_start_queue] <= SelectedDate && ( TicketsTable[ticket_end_queue] > SelectedDate || ISBLANK ( TicketsTable[ticket_end_queue] ) ) ) )
3. In the Table visual add the Date from Calendar and OpenTicket Measure.
4. Ready
Regards
Victor
Hi, Try with this:
1: Create a CalendarTable. Dont related to ticket table.
2. Create a measure
OpenTickets = VAR SelectedDate = SELECTEDVALUE ( 'Calendar'[Date] ) RETURN CALCULATE ( COUNT ( TicketsTable[ticket_id] ), FILTER ( TicketsTable, TicketsTable[ticket_start_queue] <= SelectedDate && ( TicketsTable[ticket_end_queue] > SelectedDate || ISBLANK ( TicketsTable[ticket_end_queue] ) ) ) )
3. In the Table visual add the Date from Calendar and OpenTicket Measure.
4. Ready
Regards
Victor
What about finding the average and max ticket age for each day? Using the data above I have this table of what I would like:
Date | Open Tickets | Average Age | Max Age |
7/23/2018 | 2 | 0 | 0 |
7/24/2018 | 2 | 1 | 1 |
7/25/2018 | 3 | 1.67 | 2 |
7/26/2018 | 5 | 0.8 | 3 |
7/27/2018 | 4 | 1.4 | 4 |
7/28/2018 | 4 | 2.4 | 5 |
7/29/2018 | 7 | 2.43 | 6 |
7/30/2018 | 4 | 3.5 | 7 |
7/31/2018 | 3 | 5 | 8 |
I want to see on a given day, out of all the available open tickets, what is the average age and the max age in days. any thoughts?? I've been playing around with some measures but can't get it to work yet.
thanks for any help again Victor!
Matt
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |