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

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.

## 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

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## Retail Management & Demand Forecasting Reports In Power BI

In this Power BI Showcase, we focus on reports that provide analysis on retail management,...

## First N Business Days Revisited – A DAX Coding Language Solution

Let's take another look at the problem discussed in this tutorial, which dynamically compared the first...

## Showcasing Multiple Selections In A Power BI Slicer

Many of you may know that we regularly conduct Power BI Challenges. There are lots of techniques that I...

## Microsoft Flows: Editing And Tracking In Power Automate

Once you understand how a Flow Diagram looks like, it will be easier to make some changes to it. In...

## Creating Power BI Reports Effectively & Avoiding Hidden Pitfalls

In this tutorial, I will discuss four hidden pitfalls in Power BI that can wreck your data model and/or...

## Power BI Page Navigation Buttons

The Power BI page navigation buttons play a critical role when it comes to storytelling. An organized...

## Power BI Report Examples And Best Practices – Part 1

In today's post, I'd like to present some Power BI report examples and best practices. In my own Power...

## Power BI Python Tutorial: How To Translate Texts

This blog will demonstrate how to perform language or text translation using Python and pipe it over...

## Measure In Power BI: Optimization Tips And Techniques

In this tutorial, youâ€™ll learn how to optimize a measure in Power BI. Optimizing measures in your...

## How To Use Power Query Row And Column Selection

This tutorial will discuss how to use selection and projection inside the Power Query Editor. Selection...

## Use Tabular Editor To Create Calculation Groups In Power BI

Today's blog post will give you an introduction to calculation groups. I'll try to answer four basic...

## Effective Data Storytelling: Asking The Right Questions

To ensure that we have a good story to tell, effective data storytelling by asking the right questions...