Calculating % of Patients Not Seen by Date

[rmcmullenNSHOA]

I'm trying to create a report that shows, for a given range of dates (e.g., 5/18/22 - 7/19/22), how many patients were in a hospital and able to be seen, how many were seen, and how many were not seen. Finally, I want to add a column showing the % that were not seen. The final result should be broken out by each distinct date in the range.

Simple enough to calculate - the struggle is getting the data in a place to get me there.

I've shared a sample data source file here.

So far, I've created a calendar table based on the minimum Admit date and the maximum Discharge date and created a relationship to the dDateTime field.

I also created a "Patient Count" measure Patient Count = DISTINCTCOUNT(VisitData[sPatientNumber]), but that's as far as I've gotten.

Any help would be greatly appreciated.

[rmcmullenNSHOA]

So I ended up solving this in excel for now. Here's how I did it:

• New tab, cell A1 = UNIQUE(Table1[sLastName]:[Discharge]). This gives me all the distinct stays by patient (i.e., if I was admitted/discharged multiple times during the period, they will show up separately).
• Calculate the Length of Stay for each row = [Discharge] - [Admit] + 1
• Calculate the number of times the patient was seen
  = COUNTIFS(Table1[sPatientNumber],B2,Table1[Admit], C2)
• Calculate the number of times the patient was not seen (i.e., Unseen)
  = Length of Stay - Seen (from above)
• Calculate the Unseen %
  = Unseen / Length of Stay

I'd love to figure out how to do this in PowerBI and create a report that I could share with others on my team.

[Anonymous]

Hi @rmcmullenNSHOA ,

Would you please share the excel file with the above?

Thanks in advance!

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[amitchandak]

@rmcmullenNSHOA , Not seen

countx(values(VisitData[sPatientNumber]), if(isblank([Patient Count]),[sPatientNumber], blank() ) )