Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
danguy2099
Frequent Visitor

Count number of patients each day

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 :

 

danguy2099_0-1648413928151.png

 

 

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

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

Picture1.png

 

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.


Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

Picture1.png

 

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.


Go to My LinkedIn Page


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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors