topic Occupancy Rate Formula in DAX Commands and Tips

Occupancy Rate Formula

Samhunt — Sun, 25 Feb 2024 12:00:32 GMT

Hi guys,

I want to calculate the occupancy rate for all properties that we manage.

So now I am using this formula that returns me a precise result for one property:

Occupancy Rate =

[Total Rented Days] / (365 - [Total Blocked Dates])

So this works well when one property is selected except of course when there is a leap year, but the difference is negligible.

What I want is to have the right occupation rate when I select more than one property from the slicer or when I dont have any selection and it aggregates the measures from all properties and that 365 remains constant.

Regards

Xanthos

Re: Occupancy Rate Formula

lbendlin — Sun, 25 Feb 2024 16:32:45 GMT

Use a proper calendar table so you can use the actual days in a year (including THIS year!).

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Occupancy Rate Formula

Anonymous — Tue, 27 Feb 2024 07:19:16 GMT

Hi Samhunt ,

The following expressions are for your reference:

Measure = IF(ISFILTERED(your slicer),[Total Rented Days] / (365 - [Total Blocked Dates]),[Total Rented Days]/365)

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Re: Occupancy Rate Formula

Samhunt — Thu, 29 Feb 2024 16:30:02 GMT

Hi lbendlin,

Please use the link below to find something similar to what I am using in my project.

https://drive.google.com/file/d/11-KXMj9XfPwSAe9gnZj_NZWsLPLfaX9Z/view?usp=drive_link

Looking forward for your reply

Regards
Xanthos

Re: Occupancy Rate Formula

lbendlin — Sat, 02 Mar 2024 02:50:04 GMT

Link asks for access. Please check.

Re: Occupancy Rate Formula

Samhunt — Sat, 02 Mar 2024 06:04:56 GMT

Apologies lbendlin ,

Now it should be ok

Re: Occupancy Rate Formula

lbendlin — Sat, 02 Mar 2024 18:54:01 GMT

Your Blocked Dates table has blanks, as has your Booking table.

Have you considered using From/To intervals or are you ok with listing individual days for each booking? (both are ok, just asking)

There is no booking ID anywhere?

Most of your date columns are still marked as text.

Re: Occupancy Rate Formula

Samhunt — Sun, 03 Mar 2024 10:53:49 GMT

Hi lbendlin,

I am an amature so I try to find the solutions to what I want to create through the forum and videos so I simply blindly follow people's suggestions 🙂

I have used this video which it made sense how to set it up according to my limited understanding of how Dax works: https://www.youtube.com/watch?v=ISDhR-TzwJk&t=1s

Maybe this way it will eventually overload my data model.

But I am up for suggestions,

Booking ID's are on a different table that shows our revenue. So I count the number of bookings from that one.

Regards

Re: Occupancy Rate Formula

lbendlin — Sun, 03 Mar 2024 16:43:36 GMT

take a deep breath, and start learning the basics of data models and data types. Blindly following other people's suggestions only gets you so far.

Re: Occupancy Rate Formula

Samhunt — Sun, 03 Mar 2024 18:17:29 GMT

Solution to the problem

Use the following formula to count the number of selected options in the slicer.

Also check this video: https://www.youtube.com/watch?v=D532_ix9qLQ

Count Selected Properties =

COUNTROWS(

VALUES( 'Properties'[Accommodation] )

)
Replace the red bold text with the field you added in your slicer. So this will count how many options you have selected and if there is no option selected will count all the number of options in the slicer.

Then add this in your occupancy formula to multiply it with the number of days.

This is my formula because we manage properties that we allow owners to block dates for their own use of the property and we want to remove those days from the total days that the property was available.

Occupancy Rate =

[Total Rented Days] / ( (365* [Count Selected Properties])- [Total Blocked Dates])

This will return you the right result. It will be off by one day for leap years personally but for me that is negligible.