Forum Discussion

pui's avatar
pui
Frequent Visitor
3 years ago
Solved

Array formula in DAX

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 pr...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    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 ()
            )
    )
)

     

     

     

pui's avatar
pui
Frequent Visitor
3 years ago

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!