# 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

## Debugging DAX: Tips and Tools for Troubleshooting Your Formulas

One of the main reasons why businesses all over the world have fallen in love with Power BI is because...

## Practical Application of TREATAS Function in DAX

A hands-on project focused on using the TREATAS function to manipulate and analyze data in DAX.

## MAXX in Power BI – A Detailed Guide

A hands-on guide to implementing data analysis projects using DAX, focused on the MAXX function and its combinations with other essential DAX functions.

## Leveraging the COUNTX Function In Power BI

Learn how to leverage the COUNTX function in DAX for in-depth data analysis. This guide provides step-by-step instructions and practical examples.

## Using the FILTER Function in DAX – A Detailed Guide With Examples

A comprehensive guide to understanding and implementing the FILTER function in DAX, complete with examples and combinations with other functions.

## DATESINPERIOD Function in DAX – A Detailed Guide

Learn how to implement and utilize DAX functions effectively, with a focus on the DATESINPERIOD function.

## Using the DISTINCT Function Effectively in DAX

A systematic exploration of the DAX DISTINCT function to optimize data analytics.

## Guide and Many Examples – ALL Function in DAX

A detailed guide to understanding, implementing, and mastering the DAX ALL function, complemented by practical examples and combinatory techniques.

## Detailed Guide to SWITCH function in DAX

A comprehensive guide to mastering DAX functions in Power BI for conducting advanced data analysis.

## SUMMARIZE Function in DAX – A Deep Dive

A comprehensive guide to using the DAX function SUMMARIZE in Power BI, with detailed explanations and practical examples.

## Your Data Visualization Doesnâ€™t Look Great. What Should You Do?

Data visualization is the key to unlocking the insights hidden within your data. But, what if your...

## Leveraging Power BI for Data-Driven Decisions

In the world of data analytics, thereâ€™s a constant demand for tools that not only help you make sense...