Here’s a dataset with 3 columns – Patient Name, Date of admission and Duration (days).
| Patient | Date of admission | Duration (day) |
| A | 10-10-2017 | 25 |
| B | 20-10-2017 | 6 |
| C | 23-10-2017 | 12 |
| D | 29-10-2017 | 9 |
The objective is to split the hospitalization per patient into different months to determine each month’s revenue accrual. The expected result is
| Length of stay | Month | ||
| Patient | October | November | Grand Total |
| A | 21 | 4 | 25 |
| B | 6 | 6 | |
| C | 8 | 4 | 12 |
| D | 2 | 7 | 9 |
| Grand Total | 37 | 15 | 52 |
I have solved this problem with the help of Power Query and PowerPivot. You may download my workbook here.
Split total patient hospitalisation days into multiple months
{ 0 Comments }