Forum Discussion

GilesWalker's avatar
GilesWalker
Icon for Skilled Sharer rankSkilled Sharer
10 years ago
Solved

Hotel occupancy rates

Hi everyone,   I have been stuck on this issue for a few days ad am probably over thinking things, so I am hoping someone out there can provide a tidy solution.   I have created some dummy data f...
Need Help
  • Anonymous's avatar
    Anonymous
    10 years ago

    Think I've got it solved for you:

     

    Add an index column to your Bookings table, since I'm assuming the GuestID refers to a specific person and that person can book multiple rooms at a time or across a given time period.

     

    Add a Date Dimension.

     

    Your model should look like the following:

     

     

     

    Create the following measures:

    Rooms Occupied:=CALCULATE(DISTINCTCOUNT(Bookings[Index Column]),Filter(Bookings,[Date_from]<=LASTDATE(DimDate[Date])&&[Date_to]>=FIRSTDATE(DimDate[Date])))

     

    Rooms Available (Total for Selected Dates):=CALCULATE(DISTINCTCOUNT(DimDate[Date]))*48

     

    Total Dates Booked:=CALCULATE(DISTINCTCOUNT(DimDate[Date]),Bookings)

     

    Total Rooms Occupied:=SUMX(Bookings,[Rooms Occupied]*[Total Dates Booked])

     

    OccupancyRate:=DIVIDE([Total Rooms Occupied],[Rooms Available (Total for Selected Dates)])

     

    Or

     

    OccupancyRate:=DIVIDE([Total Rooms Occupied],[Rooms Available (Total for Selected Dates)])*DIVIDE([Rows in Bookings],[Rows in Bookings])

     

    Pivot Table Examples:

     

     

    Please mark it as a solution or give a kudo if it works for you, otherwise let me know if you run into an issue and I'll do my best to assist. Go To bipatterns.com for more techniques and user guides.

     

    Thanks,

    Ryan Durkin

     

Anonymous's avatar
Anonymous
Not applicable
10 years ago

GilesWalker

This is a pretty interesting request.  If you have a dummy excel file available, I'd be happy to provide a solution.  Attach it here if possible.  In order to help I'll need a better understanding of the data model in this case.

 

 

  • GilesWalker's avatar
    GilesWalker
    Icon for Skilled Sharer rankSkilled Sharer
    10 years ago

    Anonymous happy to, how do I do that?

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      GilesWalker So that you don't have to wade through that other thread to pick the best of the offered solutions, I'd recommend the following:

       

      First, that relationship between Date_from and the date table. There should be no table relationships for this to work. Again, see that other thread for the details. Then here's your occupancy measure:

       

      Occupancy = CALCULATE(
      DISTINCTCOUNT(TableName[Room_ID]),
      FILTER(
      TableName,
      TableName[Date_from] <= LASTDATE(DateTable[Date]) &&
      TableName[Date_to] >= FIRSTDATE(DateTable[Date])
      )
      )

       

      You can place any time period from the date table next to this measure and it will work. A month, a week, a single day... I have it counting each room only once, but you may want to do a distinctcount of guests instead if you want to count the same room multiple times if it is occupied multiple times in a period. Or you could do a regular count of rooms instead of a distinctcount. Depends on your specific needs.

      • GilesWalker's avatar
        GilesWalker
        Icon for Skilled Sharer rankSkilled Sharer
        10 years ago

        Thanks Anonymous I just finished reading through the information on you link and your idea seems nice and clean. Once I get into work in a couple hours I will try both yours and Sean idea to see what works best.

         

        I will reply back with what I have done.

         

        Giles