Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
I have some patients data in a hospital with admission date and discharge date. I need to calculate how many patients are in the hospital on each day. Sample data and expected output as below, I produced the expected output by excel array formula, no idea how to do it in Power BI.
Logic
1. Count the admission date but not discharged date, e.g. the first patient is admitted on 1/1 and discharged on 3/1, he is counted as a patient on 1/1 and 2/1
2. Blank discharge date means patient is not yet discharged, so he is counted until the current date
Sample data
Admission date (dd/mm/yyyy) | Discharged date (dd/mm/yyyy) |
01/01/2023 | 03/01/2023 |
02/01/2023 | |
03/01/2023 | 05/01/2023 |
04/01/2023 | 12/01/2023 |
05/01/2023 | 10/01/2023 |
Expected output
Date | No. of patients |
01/01/2023 | 1 |
02/01/2023 | 2 |
03/01/2023 | 2 |
04/01/2023 | 3 |
05/01/2023 | 3 |
06/01/2023 | 3 |
07/01/2023 | 3 |
08/01/2023 | 3 |
09/01/2023 | 3 |
10/01/2023 | 2 |
11/01/2023 | 2 |
12/01/2023 | 1 |
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
No of patients measure: =
COUNTROWS (
FILTER (
Data,
Data[Admission date (dd/mm/yyyy)] <= MAX ( 'Calendar'[Date] )
&& OR (
Data[Discharged date (dd/mm/yyyy)] > MIN ( 'Calendar'[Date] ),
Data[Discharged date (dd/mm/yyyy)] = BLANK ()
)
)
)
Hi @Jihwan_Kim
Thank you so much for your quick reply! It's very helpful, I've replicated to my dataset. A follow-up question - Can I calculate the monthly average of the no. of patients measure? (The sample data contains only Jan 2023 but the real data contains years of data)
Thank you!
Hi,
Please check the below picture and the attached pbix file.
No of patients measure: =
COUNTROWS (
FILTER (
Data,
Data[Admission date (dd/mm/yyyy)] <= MAX ( 'Calendar'[Date] )
&& OR (
Data[Discharged date (dd/mm/yyyy)] > MIN ( 'Calendar'[Date] ),
Data[Discharged date (dd/mm/yyyy)] = BLANK ()
)
)
)