Forum Discussion

Samhunt's avatar
Samhunt
Icon for Helper II rankHelper 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 = [To...
  • 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. 
     
  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Link asks for access. Please check.

    • Samhunt's avatar
      Samhunt
      Icon for Helper II rankHelper II
      2 years ago

      Apologies lbendlin , 

       

      Now it should be ok

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

        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.