How To Calculate Occupancy Days Per Month In Power BI Using DAX

10 comments

This tutorial highlights how we’re able to calculate occupancy days using DAX in Power BI. This is quite helpful for organizations like hospitals, Airbnb, hotels, etc. This is an interesting insight if you want to dive into managing your occupancy levels effectively. You may watch the full video of this tutorial at the bottom of this blog.

This topic came through the Enterprise DNA support forum that I think deserves a deeper look. I really enjoyed working through the solution, so I thought it’s a great idea to share it in a tutorial. The scenario is to work out how many days within any month a person occupied something, so it might be a hospital bed, a shared library, or something else.

In this demonstration, we’re looking into the occupancy of hospital beds. We’ll be working between two dates – the Admit date and the Departure date. We have the patients’ names, the months, and the number of days the patients occupy the rooms or beds.

calculate occupancy

Data Model Set Up

This is quite a tricky scenario that many people get confused about, but it’s important to understand how to solve this because it’s a common real world scenario.

We’re working with two dates here, but this is just a demo data for this example solution. Within a month and/or two, we’ll find out how many days these patients stay in the hospital.

calculate occupancy

This is the data model set up when we’re creating inactive relationships, which we need for this scenario. We have the Dates table that doesn’t change at all, but we have these inactive relationships that allow us to work some logic through the Data table and bring in some context from the Dates table.

the data model

It’s good practice to have inactive relationships and set up the model this way because we might want to do some other calculations in our fact table and create contexts from the Dates table in our other analyses. This is where that USERELATIONSHIP function comes in handy. This function enables us to turn on these relationships.

We need to use inactive relationships when working with multiple dates in the fact table. 

The Dates table we’re using here is a standard one, which has all of the things we need for this analysis. We’re going to use the MonthInCalendar column in our calculation.

Dates table

DAX Formula To Calculate Occupancy Days In A Month

In our formula, we work through every single month to calculate a few IF statements. We use variables (VAR) to create the parameters in which we’ll use in our IF statements. 

Remember that with DAX, every result is calculated individually. So for example, the result 14 days here (in our table below) calculates differently to the result 18 days. This is because the context in which these results are calculating is different.

calculate occupancy

They may have the same context in terms of the patient (238), but the context based on the months (August and September) is different. The interesting thing about context here is that there’s nothing coming naturally via an active relationship. We have to generate some values from what we have in our matrix with DAX formula. 

So we need to work out our variables first. We compute our AdmitDate and DepartureDate. Then, we work out our MinDateInContext and the MaxDateInContext, which are calculating the 1st and the 30th or 31st days of a certain month (28th or 29th for February). The second part of the formula is where the logic comes in to calculate occupancy days in a month or within months.

Conclusion

There’s a little bit to it, but hopefully you get the logic to calculate occupancy days in Power BI. You can try it out yourself and see how DAX enables you to work out inactive relationships and multiple dates. 

This is a quite unique scenario, but realistic. Businesses that need to manage occupancy levels could benefit from this solution. This shows the analytical abilities of Power BI with the power of DAX formulas.

Good luck with this one!

Sam

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events

membership banner 3

***** Related Links*****
How To Work With Multiple Dates In Power BI
Calculate Rolling Totals Using DATESBETWEEN In Power BI
Show Results Up To Current Date Or A Specific Date In Power BI

***** Related Course Modules*****
Business Analytics Series
Time Intelligence Calculations
Advanced Data Transformations & Modeling

***** Related Support Forum Posts*****
How To Create New Measure That Calculates Occupancy Per Month Between Two Date Columns?
How To Allocate Days Into Months Using A Start And End Date As A Measure
Lost/New Customers Each Year-Month
For more occupancy queries to review see here…..

Enterprise DNA Events

10 comments on “How To Calculate Occupancy Days Per Month In Power BI Using DAX”

  1. Hi Sam, this is amazing. I cannot understand how we can use the data after to calculate occupancy in % and use the filters.

    1. Hi, thanks for your interest in EDNA Blogs.

      There are number of functions like SUMX, SUMMARIZE that can help you in calculating Total days to collect percentages.
      Further for filters there are functions like CALCULATE, ALLSELECTED etc. to control context.

      Please refer to below post or explore similar posts on EDNA Forum.
      https://forum.enterprisedna.co/t/how-to-create-new-measure-that-calculates-occupancy-per-month-between-two-date-columns/2650/2

      Alternatively, raise a new ticket with exact requirement and one of our EDNA experts/members will help you.

      For similar Analytical Scenarios, explore https://portal.enterprisedna.co/p/business-analytics-series

  2. I wanted to calculate the average out of the total for each month, can u explain here how that can be done?

    1. Hi Divakar sabastin – Thanks for your interest in Enterprise DNA Blogs.
      For getting Average for each month, need to encapsulate the formula inside Summarize along with AverageX as given below.

      Avg Occupied Days WithIn Month =
      AVERAGEX(SUMMARIZE(ALLSELECTED(data),Data[PATIENT],Data[Admit],Data[Departure],”@value”,VAR AdmitDate = VALUE( SELECTEDVALUE( Data[Admit] ) )
      VAR DepartureDate = VALUE( SELECTEDVALUE( Data[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() ) )),[@value])

      Hope this helps. Please feel free to ask any other query related to this Blog Post.

  3. Hi Sam,
    Its excellent . I have the same scenario where i need to split total days on monthly basis. I can see there are similar dates like admit and departure date are same , in that case its showing as 0, what should i do if I want to show as 1 rather 0. and how can we show the same using time period slicers. ca you please help me on this

    1. Hi Srinivas,

      Thanks for your interest in Enterprise DNA Blogs.

      Not possible to provide a Solution without knowing exact requirements and checking the DataSet/Model/Visuals.

      For help with Solution, Raise a request at EDNA Forum https://forum.enterprisedna.co/ with sample PBIX file and our team of experts will help you.

      Please feel free to ask any other query related to this Blog Post.

  4. Hi Sam
    Its sorted . Your Blog for the Occupancy of Days helpme in resolving my issue . Only thing is when start & end dates are same and start date falls on month end and last date falls on first of month in that case , i have to tweak the code accordingly. But it was excellent Sam. thanks alot. Really appreciate it.

  5. its great!

    I have similar issue but in my case the patient number is a name (string) and this name is duplicated, I mean the same person has two or more different periods of dates, I follow this but the matrix only show the names that appear only one time in the table, is possible to show also the rest of the names?

    thanks a lot

    1. Hi Marcos,

      Thanks for your interest in Enterprise DNA Blogs.

      For your requirement, try performing below steps.

      In Fact table, add an Index column this can be done through Power Query
      Create a Dimension table, only with Patient Name and create relationship with Fact table.
      In the visual, use Patient Name from Dimension table and In Measure, use Summarize function to calculate Occupancy for each stay.
      Sum the summarize function to get using SUMX

      Above shall get final occupancy for each Patient. If above is not clear or haven’t used these functions, then raise a Ticket at EDNA forum https://forum.enterprisedna.co/ with sample PBIX for help.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.