Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 Num | CreatedDate | Closed Date |
12 | 8/4/2015 | |
85 | 9/8/2015 | |
29 | 8/14/2015 | |
34 | 9/4/2015 | |
79 | 8/29/2015 | 8/29/2015 |
60 | 9/18/2015 | 12/19/2015 |
The expected results would be:
<30 Days | 30-59 Days | 60-89 Days | 90+ Days | |
August 2015 | 3 | |||
September 2015 | 3 | 2 | ||
October 2015 | 3 | 2 | ||
November 2015 | 3 | 2 | ||
December 2015 | 5 | |||
January 2016 | 4 |
Solved! Go to Solution.
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 ) ) )
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.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |