Count number of patients each day

[danguy2099]

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 · Accepted solution]

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

[danguy2099]

This solution worked out beautifully.  Simple, but truly effective solution. Thanks a lot !!!

[amitchandak]

@danguy2099 , one of these two blogs on mine should help you

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...