Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mrouton
Frequent Visitor

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
1 ACCEPTED SOLUTION
Anonymous
Not applicable

@mrouton,

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-ca..., 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
        )
    )
)

View solution in original post

5 REPLIES 5
parry2k
Super User
Super User

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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.

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)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@mrouton,

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-ca..., 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
        )
    )
)

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.