Average Tickets Age Over Month

[Anonymous]

Hi All,

I have created Complaint status as New, WIP and closed based on date table and complaints table.

Now i need a trend line for avg age of open complaints for every month. I have a separate date table and Complaints table.

Please find the sample as below:

Sample dataset:

Complaint Nbr  CreatedMonth  ClosedMonth   Status  Created Date  Closed Date 

C1                         Jan 2019         Jan 2019         New      1/2/2019         1/28/2019

C2                         Dec 2018                               WIP       12/15/2018      

C3                         Jan 2019                                New      1/24/2019

C4                         Dec 2018        Jan 2019        Closed   12/18/2018     1/15/2019

[amitchandak]

@Anonymous , it would like this.

calculate(averageX(values(Complaint Nbr), datediff(min(Table[Created Date]),max(Date[date]),Day)), crossfilter(Date[date], Table[Created Date]))

Assumed active join with date table is Table[Created Date]

and you have taken same approach as my HR blog:https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

Hi @amitchandak ,

And the formula which you provided averagex(values(complaint Nbr) gives avg count of complaints?

I am sorry i am trying to figure it out for the first time.

I need to calculate average number of days for open ticket in a month.

[amitchandak]

@Anonymous ,

calculate(averageX(values(Complaint Nbr) // Group data at Nbr and then Take avg

, datediff(min(Table[Created Date]),max(Date[date]),Day)), crossfilter(Date[date], Table[Created Date]) // Date diff with Max date , in case month month end date , open to max date in the row or date selected. in days. Avg of that is needed 

)

[Anonymous]

Hi @amitchandak ,

Thanks for the clarification on Average function in DAX.

I got more clarification on the average age for complaints. Instead of date difference between Closed & Created Date, i need to find the average value for the below:

I need to calculate average of date difference between

1.  Cust contact Date & Analysis Date

2. if (Analysis Date is blank) then difference between (Cust Conatct Date & Closed Date)

3. if (Analysis Date is blank) & (Closed Date is blank) then Date difference between (Cust Contact Date & Credit Issued Date)

Note: I dont have relationship between my calendar table & date table.

Kindly let me know if it is feasible.

Thanks in Advance!!

[Anonymous]

The above mentioned calculation is feasible but i need to know how to equate this calculation from complaint table to the Date table since i am using the date from Date table in my chart as x-axis.

[Anonymous]

Hi @amitchandak ,

i didnt link the two tables since i have derived calculation for New, wip and closed as below:

1) New1 = CALCULATE(DISTINCTCOUNT(Data[Complaint Nbr]), Data[Creation Date] in DISTINCT('Open_Close DateTable'[Date]))

2) WIP =

VAR minDate =

MIN ( 'Open_Close DateTable'[Date])

RETURN

CALCULATE (

DISTINCTCOUNT ( Data[Complaint Nbr] ), NOT(ISBLANK(Data[Creation Date])),

Data[Creation Date] < minDate, ISBLANK(Data[Closure Date])

)

3) Closed = VAR mindate = Min('Open_Close DateTable'[Date]) return calculate(DISTINCTCOUNT(Data[Complaint Nbr]), Data[Closure Date] in DISTINCT('Open_Close DateTable'[Date]), Data[Creation Date] < mindate)

Without linking the two tables, is it possible to achieve the above average age for complaints?

Thanks in advance!