Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table of cases which has an open date, on hold date and close date in it. It is refreshed each month.
I need to be able to create a table which shows the number of still open cases (not closed and not on hold) at the end of each month split into ranges according to the number of days the case is open.
Please could someone help?
open days/asofdate | 9/30/2019 | 10/31/2019 |
0-30 days | 20 | 30 |
31-60 days | 15 | 20 |
61-90 days | 10 | 11 |
91-120 days | 12 | 12 |
120+ days | 9 | 9 |
Hi @Anonymous ,
I still a little confused about your data sample and your requirement.
I need to be able to create a table which shows the number of still open cases (not closed and not on hold) at the end of each month split into ranges according to the number of days the case is open.
If it is convenient, could you share your desired output so that I could understand your logic better?
Best Regards,
Cherry
Source data I have described just in words, the table at the end was the target table I need. Sorry for confusion.
Here is the sample for better understanding. Next to the Source data I have added the calcualtion I used to create the sample target table - so that is clear the logic for counting open days.
Source data | Calculation logic | |||||
Case code | Open Date | On hold date | Close date | # number of Open days on 31-08-19 | # number of Open days on 30-09-19 | |
A | 05-07-19 | 57 | 87 | |||
B | 02-01-19 | 02-02-19 | ||||
C | 25-08-19 | 6 | 36 | |||
D | 15-06-19 | 30-08-19 | ||||
E | 06-05-19 | 117 | 147 | |||
F | 17-08-19 | 20-08-19 | ||||
G | 06-09-19 | 10-09-19 | ||||
H | 23-09-19 | 7 | ||||
I | 07-07-19 | 06-09-19 | 55 | |||
J | 25-07-19 | 37 | 67 | |||
K | 01-08-19 | 20-09-19 | 30 | |||
L | 20-09-19 | 10 | ||||
M | 02-02-19 | 03-03-19 | ||||
N | 05-03-19 | 179 | 209 | |||
O | 06-09-19 | |||||
Target table | ||||||
Ranges of Open status duration | the count of Open Cases on the 31-08-19 | the count of Open Cases on the 30-09-19 | ||||
0-30 days | 2 | 2 | ||||
31-60 days | 3 | 1 | ||||
61-90 days | 0 | 2 | ||||
91-120 days | 1 | 0 | ||||
120+ days | 1 | 2 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |