The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.
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 ()
)
)
)
User | Count |
---|---|
14 | |
11 | |
8 | |
6 | |
5 |
User | Count |
---|---|
28 | |
19 | |
14 | |
8 | |
5 |