Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Ticket Aging Trend

I have 2 tables (one is a Calendar table and the other is a table of tickets).  I am trying to create a chart that shows on a monthly basis, how many tickets fall into different aging buckets, such as open less than 30 days, 30-59 days, 60-89 days, and 90+ days.  I am not having much luck with coming up with a measure to return the needed results.  Any help would be much appreciated.

 

Here's some sample data:

Ticket NumCreatedDateClosed Date
128/4/2015 
859/8/2015 
298/14/2015 
349/4/2015 
798/29/20158/29/2015
609/18/201512/19/2015

 

The expected results would be:

 <30 Days30-59 Days60-89 Days90+ Days
August 20153   
September 201532  
October 2015 32 
November 2015  32
December 2015   5
January 2016   4
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Anonymous,

    Extending parry2k's idea with the pattern from https://www.powerpivotpro.com/2013/04/counting-active-rows-in-a-time-period-guest-post-from-chris-campbell/, you can get close with this sort of approach (change the DATEDIFFs to suit), though it's nasty messy:

    30-59 days = 
CALCULATE (
    COUNTROWS ( 'Tickets' ),
    FILTER (
        Tickets,     
            Tickets[CreatedDate] <= LASTDATE ( Dates[Date] )
                && (
                    Tickets[Closed Date] >= FIRSTDATE ( Dates[Date] )
                        || Tickets[Closed Date] = BLANK ())
                && (
        		DATEDIFF (
                    Tickets[CreatedDate],
                    IF (
                        Tickets[Closed Date] = BLANK ()
                            || Tickets[Closed Date] > LASTDATE ( Dates[Date] ),
                        LASTDATE ( Dates[Date] ),
                        Tickets[Closed Date]
                    ),
                    DAY
                )
                    >= 30
                && DATEDIFF (
                    Tickets[CreatedDate],
                    IF (
                        Tickets[Closed Date] = BLANK ()
                            || Tickets[Closed Date] > LASTDATE ( Dates[Date] ),
                        LASTDATE ( Dates[Date] ),
                        Tickets[Closed Date]
                    ),
                    DAY
                )
                    < 60
        )
    )
)

5 Replies

  • parry2k's avatar
    parry2k
    Super User
    9 years ago

    Add a aging column in your ticket table and an aging group column 

     

     

    Aging = datediff(Table1[Date], today(), day)
    AgingGroup = if(Table1[Aging]<31, "< 30 Days", if(Table1[Aging]>=31 && Table1[Aging] <60, "30-59 Days", if(Table1[Aging]>=60 && Table1[Aging]<90, "60-89 Days","90+Days")))

    You can ofcourse combine both in one formula, you can also use switch instead of if condition. Just sharing the concept.

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Unfortunately this is not going to give me what I need.  First, I get an error for Aging if the ClosedDate is blank.  Second, this will only give me Aging from today.  If a ticket is created in August, but not closed until January, then it needs to be in the <30 bucket for August, and then in the 30-59 bucket for September, and then in the 60-89 bucket for October, and then in the 90+ bucket until it is closed.

      • parry2k's avatar
        parry2k
        Super User
        9 years ago

        That was just an idea, you can add condition to check blank() and make calculation work.

         

        Here is revised formula, checks if close date is blank then use today's date otherwise use close date

         

        Aging = datediff(Table1[Date], if(Table1[CloseDate] = blank(), today(), Table1[CloseDate]),DAY)