One thing I would recommend here first is actually attempt to give it go in the model. The best way to learn is to just start writing DAX, create models etc.
It will also speed up how we can assist you.
First you need a date table (always). Not going to get very far without one here.
What you ultimately need to solve this is derivative of the ‘events in progress’ pattern that can be found below. It’s all about managing multiple dates in your fact tables (like we have here)
Here’s what I created for this one.
I initially just built it out, bit by bit, so I could see all the numbers in a table. I always recommend this
Now all that’s required is the logic to account for each within month number.
This actually took a while to work through this, but I think I have it now
Occupied Days WithIn Month =
VAR AdmitDate = VALUE( SELECTEDVALUE( Table1[Admit] ) )
VAR DepartureDate = VALUE( SELECTEDVALUE( Table1[Departure] ) )
VAR MinDateInContext = VALUE( MIN( Dates[Date] ) )
VAR MaxDateInContext = VALUE( MAX( Dates[Date] ) )
IF( AND( AdmitDate < MinDateInContext, DepartureDate > MinDateInContext ) ,
MIN( DepartureDate, MaxDateInContext ) - MinDateInContext,
IF( AND( AND( AdmitDate > MinDateInContext, AdmitDate < MaxDateInContext ), DepartureDate > MinDateInContext ),
MIN( DepartureDate, MaxDateInContext ) - AdmitDate,
BLANK() ) )
Initially I worked it out via a table.
Then I placed it into a matrix which looks a bit better.
I’ve checked it a little and I think it all works. You’ll have to dig deeper.
I’ve attached the model.
Occupancy.pbix (139.7 KB)