Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Average Tickets Age Over Month

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

Need Help

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

      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's avatar
        amitchandak
        Icon for Super User rankSuper User
        5 years ago

        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 

         

        )