Solved: Count number of patients each day

danguy2099 · ‎03-27-2022

Count number of patients each day

a minute ago

Hi

I need to help to make a count of patients that are in beds each day in hospitals. My data looks like this:

Hospital Patient_ID Admission_date Discharge_date

HospitalA 1 10.05.2021 13.05.2021
HospitalB 2 11.05.2021 16.05.2021
HospitalA 3 08.05.2021 15.05.2021

My expected output is like this:

date count_of_patients
07.05.2021 0
08.05.2021 1
09.05.2021 1
10.05.2021 2
11.05.2021 3
12.05.2021 3
13.05.2021 3
14.05.2021 2
15.05.2021 2
16.05.2021 1

and my final diagram is like this with possibility to have a slicer for hospital :

How can I make a measure in DAX that gives me what I want?

Jihwan_Kim · ‎03-28-2022

Hi,

Please check the below picture and the attached pbix file.

Patient Count: =
CALCULATE (
    COUNTROWS ( Data ),
    FILTER (
        Data,
        Data[Admission_date] <= MAX ( 'Calendar'[Date] )
            && Data[Discharge_date] >= MIN ( 'Calendar'[Date] )
    )
)

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.

Visit my LinkedIn page

Outlook Booking Schedule a short Teams meeting to discuss your question

View solution in original post

Jihwan_Kim · ‎03-28-2022

Hi,

Please check the below picture and the attached pbix file.

Patient Count: =
CALCULATE (
    COUNTROWS ( Data ),
    FILTER (
        Data,
        Data[Admission_date] <= MAX ( 'Calendar'[Date] )
            && Data[Discharge_date] >= MIN ( 'Calendar'[Date] )
    )
)

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.

Visit my LinkedIn page

Outlook Booking Schedule a short Teams meeting to discuss your question