How to calculate daily population in DAX AND be able to slice it by columns

Question

Updated Problem: 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.

I'm using two tables: 1) a main table with the patient data containing columns for booking date, release date, gender, provider, and race and 2) a date table (calendar) and have established a relationship between the two tables. The date table is date dimension.

Based on recommendations for my initial question, I created a calculated column in the calendar table using formula below.

PatientCount :=
SUMX (
    Patients,
    IF (
        Patients[Booking Date] <= 'Calendar'[Date]
            && Patients[Release Date] >= 'Calendar'[Date],
        1,
        0
    )
)

I then created an aggregate measure to compute the average daily population. This works; however, using this method I cannot figure out how to slice it by the patient table columns (e.g. gender, provider, race) since the calculated column is in the calendar table.

Answer 1

Based on this test data:

And a Calendar table with a 1:* relationship to the 'Patients'[Adm], you can write the following measure:

Active Patients = 
var currentDate = MAX('Calendar'[Date])
return 
MAXX(
    DISTINCT('Calendar');
    CALCULATE(
        COUNTROWS('Patients');
        ALLEXCEPT(Patients; Patients[Gen]); //Add all columns you will slice on
        Patients[Adm] <= currentDate;
        Patients[Rel] >= currentDate
    )+0
)

You can produce this table:

Which also can be sliced by Gender: