How to calculate daily population in DAX

Question

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

Hi Eric & welcome to StackOverflow! Have you marked the Date table as a DATE dimension? Sometimes that's all it takes — Hila DG

Aldert Aldert · Accepted Answer · 2019-05-03T06:29:26

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

How to calculate daily population in DAX

1 Answers