Getting a running total of current open Tickets

[jburley]

So i have two columns that are both filled with Dates, one Column has the date that a ticket was submitted and the other has the date a ticket was completed. I need to be able to get the Data for how many tickets are open on a current date. For example, if 3 tickets were open today, and none were closed the total would be 3, if 2 tickets were opend the next day and 4 were closed the running total would be 1. Does anyone have any idea how I would be able to accomplish this? Thank you!!

[jburley]

I have 2 columns, one was the request date that a ticket was put in, the other was a completion date where the ticket was finished.

I need to be able to have a running total of the amount of open tickets. For example

Date Start | Date End | Tickets to Date

1/1/18      | 1/2/18      |          2

1/1/18      | 1/2/18      |          2

1/2/18      | 1/2/18      |          3

1/3/18      | 1/7/18      |          1

Does anyone Have any idea how functionality like this could be accomplished? 

[marcussyliu]

Here is the updated ones. calculated column ccCountOfOpenTickets = COUNTROWS ( FILTER ( Ticket, Ticket[Opened] <= EARLIER ( Ticket[Opened]) && ( Ticket[Closed] > EARLIER ( Ticket[Opened] ) || ISBLANK ( Ticket[Closed] ) ) ) ) measure mCountOfOpenTickets := VAR LastSelectedDate = LASTDATE(Ticket[Opened] ) RETURN IF ( MIN('Date'[Date]) <= MAX ( Ticket[Opened] ), CALCULATE ( COUNTROWS ( Ticket ), FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ), Ticket[Closed] > LastSelectedDate || ISBLANK(Ticket[Closed] ) ) )

[Thurmus]

I am tring to get the Measure working as I think what you have done is what I need. I get an error as follows:

MdxScript(Model) (26, 69) Calculation error in measure 'ServiceTickets'[mCountOfOpenTickets]: A date column containing duplicate dates was specified in the call to function 'LASTDATE'. This is not supported.

The column I am looking at is the Date Entered column for the ticket.

Thanks in advance.

[marcussyliu · Accepted solution]

You can consider this one.

CountOfOpenTicketsParamMeasure =
IF ( MAX ( 'Date'[Date]) <= MAX(Ticket[Opened] ),
COUNTROWS (
    FILTER (
        Ticket,
        Ticket[Opened] <= MAX('Date'[Date])  &&
            ( Ticket[Closed] > MAX('Date'[Date]) || ISBLANK( Ticket[Closed] ))
        )
)
)

This is yet to be optimized. Hope this helps.

[marcussyliu]

If you just want to get the count of open tickets as of today,  you can consider this measure

CountOfOpenTicketsAsOfToday =
CALCULATE ( COUNTBLANK (Ticket[Closed] ), Ticket[Opened] <= TODAY() )

If yout want to get the data for each selected date, you can use either a calculated column or a measure

CountOfOpenTicketsParamMeasure =
IF ( MAX ( 'Date'[Date]) <= MAX(Ticket[Opened] ),
COUNTROWS (
    FILTER (
        Ticket,
        Ticket[Opened] <= MAX('Date'[Date])  &&
            ( Ticket[Closed] > MAX('Date'[Date]) || ISBLANK( Ticket[Closed] ))
        )
)
)

CountOfOpenTicketsCalColum =
COUNTROWS (
    FILTER (
        Ticket,
        Ticket[Opened] <= EARLIER(Ticket[Opened]) &&
            ( Ticket[Closed] > EARLIER( Ticket[Opened]) || ISBLANK( Ticket[Closed] ))
        )
)

This is yet to be optimzed. Hope this helps