Forum Discussion

Samhunt's avatar
Samhunt
Helper II
2 years ago
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 =
[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
 
 
  • Samhunt's avatar
    Samhunt
    2 years ago

    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. 
     

9 Replies