Number of days a bed

[jpvernon67]

Hi - I have been trying to figure this out for awhile and have tried other solutions but none of them have worked. The situation is we have 8 houses that we manage. Seven of them have 5 beds and 1 has 6. What I need to find out is how days in any given month is someone occupying a bed. These are long term facilities so I also need to include people who have not been discharged yet. The highlighted table below is what I am trying to get to. At the bottom of the page is a link to the tables and also the spreadsheet I have used to calculate this by hand. Thank you.

Sample report 

[v-henryk-mstf]

Hi @jpvernon67 ,

Can you provide a screenshot of the desired result and describe it so that I can answer it for you as soon as possible?

Looking forward to your reply.

Best Regards,
Henry

[jpvernon67]

The only thing I have is the screen shot from excel above but I'll break it down better. The first table is the sum of the number of days each bed was occupied in any given month. For people who are admitted mid-month this is the number of days from the admission date until the last day of the month. If the person was in the bed all month it is the first day of the month through the last day of the month, and for people discharged during the month it is the first day of the month until the discharge date. I sum all of the person data to get the total days used by house.

This table is a reference table which is the total bed days available in a given month per house.

The result that I need is dividing the bed days used by the total available.

this is a link to the power bi sample data/report:

https://1drv.ms/u/s!AptRNMy-TRKq4wvd6mrjQTUyIacP?e=OMBu1O