Active Tickets

Active Tickets

Active Items, Open Tickets, Outstanding Items – All names for basically the same calculation. In this video, we will discuss how to use DAX to enable the count of any “Active” item. In the sample data, there is only a stage created date and no stage end date, so we first create a stage expire date as a calculated column, then leverage the new column in our Active Ticket measure. Finally, we will display our Active Ticket measure in a gantt chart visualization created using a Power BI matrix.

Note: If your data has both a create and end (expire) date per line, then you can skip to the Active Ticket measure at timestamp 9:40 in the video.

Just the Code:

1. Setup: In my sample data, each row contains a Ticket, Stage and Create Date – but no stage end or Expire Date. There is a Closed date but only for the last stage of the Ticket, not the intermediate stages.
2. Create calculated column LowIsNew – ranking all stages within a ticket from newest to oldest

   LowIsNew =
     CALCULATE (
       COUNTROWS ( Ticket_History ),
       FILTER (
         ALL ( Ticket_History ),
         Ticket_History[Ticket] = EARLIER ( Ticket_History[Ticket] )
           && Ticket_History[Created Date] > EARLIER ( Ticket_History[Created Date] )
       )
     )    + 1

3. Create second calculated Column RowExpiration which calculated the Created Date for the next event and sets it as the EXPIRE var (varaiable). The IF in the Return also checks if the status of the row is Closed, in which case the EXPIRE is replaced by Closed date.

   RowExpiration =
     VAR EXPIRE =
       CALCULATE (
         MIN ( Ticket_History[Created Date] ),
         FILTER (
           ALL ( Ticket_History ),
           Ticket_History[Ticket] = EARLIER ( Ticket_History[Ticket] )
             && Ticket_History[LowIsNew] = ( EARLIER ( Ticket_History[LowIsNew] ) - 1 )
         )
       )
     RETURN
       IF (
         Ticket_History[Status] = "Open",
         IF ( ISBLANK ( EXPIRE ), TODAY (), EXPIRE - 1 ),
         Ticket_History[Closed Date]
       )
   

4. In your data model, ensure you have a date table, mine is Dates, that has a date field with contiguous values for all of the facts in the Ticket table. The key item here is DO NOT CREATE A RELATIONSHIP BETWEEN Dates AND Tickets.
5. Finally, create Measure Active Tickets which will count the Active Tickets for each day. This measure is explained in detail starting at 9:40 in the video.

   Active Tickets = CALCULATE (
       DISTINCTCOUNT( Ticket_History[Ticket]) ,
       FILTER (
           Ticket_History,
           (
              Ticket_History[Created Date] <= MIN ( Dates[Date] )
                   && Ticket_History[Row Expiration] >= MAX ( Dates[Date] )
                  
           )
       ),
       VALUES (Ticket_History[Ticket]))

6. In the video we transform a simple matrix of counts into a Gantt chart using conditional formatting.

And there you have it! Use this technique to calculate the counts of ANYTHING which has a begin and end date (even if it does not have an end date).

If you would like to play with the Power BI Desktop file, you can find it here.