Binning Elements (Unique Client IDs) Based on Summation of Another Element (Hours of Service)

[rfickes]

I will try to lay this out as clearly as I can.

I have a database where each row indicates a unique visit that a client received from a caregiver. Each visit includes important elements such as the duration of the visit, the date of the visit, and a client ID.

I need to pull out of this and get into a stacked bar histogram count of the number of clients in any given week who received 0.00 - 9.99 hours of care, 10.00 - 40.00 hours of care, and 40.01+ hours of care.

Binning the dates into weeks is obviously not difficult - create a group with a bin size of 7 days. However, I'm not sure how to create the bins for hours of care received, because the hours of care will have to be summed across multiple visits in a given week.

Any assistance would be greatly appreciated.

Sample Data: 

Visit ID	Client ID	Date	Hours
1	4	1/1/2018	2
2	1	1/3/2018	9
3	3	1/5/2018	3
4	2	1/7/2018	10
5	5	1/9/2018	10
6	1	1/11/2018	2
7	3	1/13/2018	4
8	5	1/15/2018	12
9	1	1/17/2018	10
10	3	1/19/2018	9
11	5	1/21/2018	3
12	5	1/23/2018	12
13	5	1/25/2018	3
14	3	1/27/2018	12
15	2	1/29/2018	8
16	5	1/31/2018	5
17	1	2/2/2018	11
18	2	2/4/2018	8
19	4	2/6/2018	6
20	1	2/8/2018	9

[jthomson · Accepted solution]

I'd look to go to Power Query and:

- assign each row a week number based on whatever periods you use

- from there, group the rows where the client ID and week number match, adding the total hours

It should then be a bit easier to get to the result you want