Forum Discussion
Solved
Occupancy Rate Formula
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 = [To...
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_ix9qLQCount 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.
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.
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.
This will return you the right result. It will be off by one day for leap years personally but for me that is negligible.
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.