Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ()
)
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 ()
)
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |