How to create new measure that calculates occupancy per month between two date columns?

DAX DAX Calculations
4

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

image
image1038×438 30.9 KB

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

RETURN
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.

image
image1094×376 25.2 KB

Then I placed it into a matrix which looks a bit better.

image
image754×659 32.1 KB

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)

Sam