I'm trying to calculate the daily population of patients at our center for any given date using the patient's booking date (start date) and their release date -- using DAX and Power Pivot. I'm planning on using it in PowerBI eventually.
I've tried the formula below. I'm not getting any errors, but when I go to create a pivot table/chart in excel, I'm not getting the correct output.
I'm only using two tables: 1) a main table with the patient data and 2) a date table (calendar) and have established a relationship between the two tables.
The booking table has Patient ID, Booking Date, Release Date columns. I'd like to be able to create a graph showing the total population by day taking both dates into consideration.
DailyPop :=
CALCULATE (
COUNTROWS ( Patients ),
FILTER (
Patients,
AND (
Patients[Booking Date] > MIN ( 'Calendar'[Date] ),
Patients[Release Date] < MAX ( 'Calendar'[Date] )
)
)
)
You can add an extra column on your date table:
PatientCount:= SUMX(Patients,IF(Patients[Booking Date]<='Calendar'[Date] && Patients[Release Date]>='Calendar'[Date],1,0))