Forum Discussion

drstrong's avatar
drstrong
Frequent Visitor
2 years ago
Solved

Measure to calculate average ticket duration

I have 2 tables, one with ticket details one with dates. I need to calculate the average age of tickets per month. My reports are filtered on the date table month column.

I have 3 inactive relationships with the date table to calculate open,closed, target closed but cannot seem to figure the average age out at all. 

My below attempts to even calculate the tickets closed same month as opened produces wrong totals.

 

CALCULATE(DISTINCTCOUNT(Bugs[issuenum_id]),

FILTER(ticket,MONTH(ticket[resolutiondate]) = MONTH(ticket[created]) &&YEAR(ticket[resolutiondate]) = YEAR(ticket[created])),

USERELATIONSHIP('date'[Date],ticket[resolutiondate]))

 

/*CALCULATE(COUNTROWS('ticket'),

FILTER(ticket,

MONTH(ticket[resolutiondate]) = MONTH(ticket[created])),

USERELATIONSHIP(ticket[resolutiondate],'date'[Date]))

*/

 

/*

CALCULATE(DISTINCTCOUNT(ticket[issuenum_id]),

MONTH(ticket[resolutiondate]) = MONTH(ticket[created]),

USERELATIONSHIP('date'[Date],ticket[resolutiondate]))*/

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi drstrong

     

    If you want to calculate the average age of tickets per month, here is the method I provided:

     

    Here's some dummy data

     

    “ticket”

     

    First, create a calculated column that counts the number of days the ticket lasted.

     

    days = DATEDIFF('ticket'[created], 'ticket'[closed], DAY)

     

     

     

    Create a measure to calculate the average age of tickets per month.

     

    average age of tickets = 
var _month = MONTH(SELECTEDVALUE(ticket[created]))
var _day = DATEDIFF(SELECTEDVALUE('ticket'[created]), SELECTEDVALUE('ticket'[closed]), DAY)
return 
CALCULATE(
    SUM('ticket'[days]), 
    FILTER(ALL('ticket'), MONTH('ticket'[created]) = _month)
) 
/ 
CALCULATE(
    COUNTROWS('ticket'), 
    FILTER(ALL('ticket'), MONTH('ticket'[created]) = _month)
)

     

     

    Here is the result.

     

     

    If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi drstrong

     

    If you want to calculate the average age of tickets per month, here is the method I provided:

     

    Here's some dummy data

     

    “ticket”

     

    First, create a calculated column that counts the number of days the ticket lasted.

     

    days = DATEDIFF('ticket'[created], 'ticket'[closed], DAY)

     

     

     

    Create a measure to calculate the average age of tickets per month.

     

    average age of tickets = 
var _month = MONTH(SELECTEDVALUE(ticket[created]))
var _day = DATEDIFF(SELECTEDVALUE('ticket'[created]), SELECTEDVALUE('ticket'[closed]), DAY)
return 
CALCULATE(
    SUM('ticket'[days]), 
    FILTER(ALL('ticket'), MONTH('ticket'[created]) = _month)
) 
/ 
CALCULATE(
    COUNTROWS('ticket'), 
    FILTER(ALL('ticket'), MONTH('ticket'[created]) = _month)
)

     

     

    Here is the result.

     

     

    If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.