Measure to calculate average ticket duration

[drstrong]

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]))*/

[v-nuoc-msft · Accepted solution]

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.