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 a...
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
        )
    )
)
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)
  • Anonymous's avatar
    Anonymous
    Not applicable
    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
        )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Thanks guys!  parry2k's idea got me started and Anonymous's response got me the rest of the way!  What I couldn't quite get was the "if" statement.