Array formula in DAX

Question

I have some patients data in a hospital with admission date and discharge date.&nbsp; I need to calculate how many patients are in the hospital on each day.&nbsp; 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.&nbsp;&nbsp;&nbsp;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/12. Blank discharge date means patient is not yet discharged, so he is counted until the current date&nbsp;Sample dataAdmission date (dd/mm/yyyy)Discharged date&nbsp;(dd/mm/yyyy)01/01/202303/01/202302/01/2023&nbsp;03/01/202305/01/202304/01/202312/01/202305/01/202310/01/2023&nbsp;Expected outputDateNo. of patients01/01/2023&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 102/01/2023&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 203/01/2023&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 204/01/2023&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 305/01/2023&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 306/01/2023&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 307/01/2023&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 308/01/2023&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 309/01/2023&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 310/01/2023&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 211/01/2023&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 212/01/2023&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1

jihwan_kim · Accepted Answer

Hi,
Please check the below picture and the attached pbix file.
&nbsp;

&nbsp;
&nbsp;

&nbsp;
No of patients measure: =
COUNTROWS (
    FILTER (
        Data,
        Data[Admission date (dd/mm/yyyy)] &lt;= MAX ( 'Calendar'[Date] )
            &amp;&amp; OR (
                Data[Discharged date (dd/mm/yyyy)] &gt; MIN ( 'Calendar'[Date] ),
                Data[Discharged date (dd/mm/yyyy)] = BLANK ()
            )
    )
)

&nbsp;
&nbsp;
&nbsp;

Forum Discussion

Array formula in DAX

2 Replies

Related content

DAX Formula Help

DAX Formula

DAX Formula

DAX Formula

Dax Formula correction help

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

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