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.
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.
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.
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.
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.
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.
***** 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
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
[youtube https://www.youtube.com/watch?v=R8tDKwimSsY?rel=0&w=784&h=441]
