Help - how to split data into ranges at the end of each month?

[Anonymous]

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

[v-piga-msft]

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

[Anonymous]

 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