Hospital Bed Census Help

[acalhoun78]

Hello

I am trying to show how many of our beds were filled on any given day and by program, i.e. "820 Stabilization".  The unique ID is the Patient ID.

Here is an example of file that the EMR exports.  

Example File 

Any assistanctane would be most appreciated. 

Thanks!

Alan

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-eachen-msft]

Hi @acalhoun78 ,

You could use DISTINCTCOUNT() function to get value of beds and use date slicer to select data range.

[acalhoun78]

Thank you for the suggestion.  I have tried that in the past, and it appears the issue is there are only two dates at play, admission and discharge.  

I have seen similar issues on this forum that involved creating a Calendar, and then writing a formula that compare the two dates.  However, I have not been able to make those work correctly.

Here is the other forum post:

I wrote my original post with very limited time, and a fair amount of frustration with not being able to solve my own issue.  I'll explain my problem to a better degree so that may present a better understanding of what I'm hoping to accomplish.

We are a non-profit substance abuse clinic with 5 locations (programs) that have beds that clients can occupy.  The financial health of those clinics is directly related to how full we can keep them.  I would like to be able to display a visual that can drilldown to see our historical census, or fill rate.  

The bit that I cannot overcome with my limit knowledge of Power BI/DAX is how do I show that number with only admission and discharge dates?

Thanks again

Alan

[mnb]

Hi Alan

I've had a similar task in a previous role.  It was a while ago and we were using SSRS for reporting but I think the principal would be the same.

I assume your data is currently a table with a row for each patient showing, patient ID, bed number, admission date, discharge date?  I'd suggest you use this and a date table to create a table which shows the patient ID, bed number and date with a row for each patient for each day they were in the bed.

Where is your data stored?  If it's in a SQL I'd suggest it might be easier to do the transformation there.

Hope this helps

Matt