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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MWinter225
Advocate IV
Advocate IV

Daily Running Total of Open Tickets in Queue

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_idticket_start_queueticket_end_queue
17/23/2018 
27/23/20187/26/2018
37/25/20187/27/2018
47/26/2018 
57/26/20187/29/2018
67/26/20187/30/2018
77/27/20187/30/2018
87/29/20187/30/2018
97/29/2018 
107/29/20187/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:

  1. ticket_id 1 has a blank ticket_end_queue date
  2. ticket_id 4 has a blank ticket_end_queue date
  3. ticket_id 5 is open on 7/27 because it falls between the start date of 7/26 and end date of 7/29
  4. ticket_id 6 is open on 7/27 because it falls between the start date of 7/26 and end date of 7/30
  5. ticket_id 7 is open on 7/27 because it falls between the start date of 7/27 and end date of 7/30

So based on the data above, the line chart (represented here in a matrix) would look like this:

DateOpen Tickets
7/23/20182
7/24/20182
7/25/20183
7/26/20185
7/27/20185
7/28/20185
7/29/20187
7/30/20184
7/31/20183

 

Is that enough info? any idea on how I can accomplish this? 

 

Thanks,

Matt

 

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

@MWinter225

 

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




Lima - Peru

View solution in original post

3 REPLIES 3
Vvelarde
Community Champion
Community Champion

@MWinter225

 

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




Lima - Peru

@Vvelarde

 

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:

 

DateOpen TicketsAverage AgeMax Age
7/23/2018200
7/24/2018211
7/25/201831.672
7/26/201850.83
7/27/201841.44
7/28/201842.45
7/29/201872.436
7/30/201843.57
7/31/2018358

 

 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

@Vvelarde Wow thanks a lot Victor! That did the trick!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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