Array formula in DAX

[pui]

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

[pui]

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!

[Jihwan_Kim · Accepted 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 ()
            )
    )
)