Ticket Resolution Rate in prior month

[Anonymous]

HI,

I have two tables, one that has ticket information (ticket) a date table (date). Within the ticket table is a Ticket_ID column, an open date (that has a relationship with the date table) and a closed date. Both opened and closed dates are in "Date" format.

I have to create a report that provides the prior calendar month's resolution rate. To get the resolution rate for the prior month, I have to determine which tickets were both opened and closed in the previous month  (i.e. since this is Jan, it would be all ticket id's that have an open date in Dec. 2018 and all ticket ids that have an opended and a closed date within the month of Dec. 2018). 

I've been attempting to get this for a few hours now but have been unable to find a solution that works....either using measures or by creating a new table.

Thank you in advance

Data Sample:

Ticket ID	Opened Date	Closed Date
1	Tuesday, October 16, 2018	Monday, December 3, 2018
2	Monday, November 26, 2018	Monday, December 17, 2018
3	Thursday, December 6, 2018
4	Thursday, December 6, 2018	Tuesday, December 18, 2018
5	Thursday, December 13, 2018	Thursday, December 20, 2018
6	Thursday, December 13, 2018	Monday, December 17, 2018
7	Thursday, December 13, 2018	Thursday, December 20, 2018
8	Wednesday, January 9, 2019	Thursday, January 10, 2019
9	Thursday, January 10, 2019

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Please try:

count open tickets =
VAR currentdate =
    TODAY ()
VAR startofpreviousMonth =
    DATE ( IF ( MONTH ( TODAY () ) = 1, YEAR ( TODAY () ) - 1, YEAR ( TODAY () ) ), IF ( MONTH ( TODAY () ) = 1, 12, MONTH ( TODAY () ) - 1 ), 1 )
VAR EndofpriorMonth =
    EOMONTH ( TODAY (), -1 )
RETURN
    CALCULATE (
        COUNT ( Sheet2[Ticket ID] ),
        FILTER (
            ALLSELECTED ( Sheet2 ),
            Sheet2[Closed Date] <> BLANK ()
                && Sheet2[Opened Date] >= startofpreviousMonth
                && Sheet2[Closed Date] <= EndofpriorMonth
        )
    )

Best regards,

Yuliana Gu

[Anonymous]

@v-yulgu-msft  - Thank you so much!!! This worked perfectly and saved me a lot of time and frusturation.

[Greg_Deckler]

See if my Open Tickets Quick Measure helps:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364

[Anonymous]

Thank you, let me give this a try....