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 = [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
Super 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.

 

Samhunt's avatar
Samhunt
Helper II
2 years ago

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

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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.

    • Samhunt's avatar
      Samhunt
      Helper II
      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.