Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Ticket Resolution Rate in prior month

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 IDOpened DateClosed Date
1Tuesday, October 16, 2018Monday, December 3, 2018
2Monday, November 26, 2018Monday, December 17, 2018
3Thursday, December 6, 2018 
4Thursday, December 6, 2018Tuesday, December 18, 2018
5Thursday, December 13, 2018Thursday, December 20, 2018
6Thursday, December 13, 2018Monday, December 17, 2018
7Thursday, December 13, 2018Thursday, December 20, 2018
8Wednesday, January 9, 2019Thursday, January 10, 2019
9Thursday, January 10, 2019 

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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