Count rows between 2 dates

[Peter2]

Hi, I have 3 tables, a Resident Location Table (Fact Table), which has columns for Person ID, Bed ID, Enter Date (the date a person takes up a bed) and an Exit Date (the date a person leaves the bed), a Location Table (Dimension Table) which has columns for Bed ID and Facility, and finally a Residents Table (Dimension Table) which has columnns for Person ID and other personal information.

What I am trying to do is determine how many people are in a Facility on a particular day, and have solved this by creating a new column in the Resident Location Date for that particular date i.e. all dates in this column are the same, and then creating another new column in the Resident Location Table that uses an IF statement to determine if the date is between the Enter Date and the Exit Date, to include the person in the Pivot, otherwise to exclude the person. A person can move from one facility to another and back depending on a variety of circumstances.

I am sure that this is an extremely cumbersome way of doing this calculation, and hopefully somebody can show me how to use a dax measure/s that will accomplish the above, so that I can use the Time Intelligence Functions to create some useful reports.

Thanks

Peter

[Greg_Deckler]

You want Open Tickets: https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147