The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
273 | |
129 | |
123 | |
101 | |
91 |