Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
stampgeorge
Regular Visitor

Calculating Daily Occupancy rate of hotel

Hello, I want to calculate the daily occupancy of two hotels. Right now, I have two tables, one is named RESORT, with two Columns, Name of Resort and Total Number of Rooms. I also have a table named Statistics, where each row represents a reservation, I also have a column called business date and I have a column with the resort name and a column named Room Nights which is always 1. 

 

Each row has only 1 reservation. The same reservation is repeated per business date. 

 

For example, if a guest arrives at 1/6/24 and checks-out at 7/6/24, they have 7 rows, one per night.

 

I have figured out how to calculate it for today, but not in total.

 

I also have a reservations table, where each reservation has only 1 row, but there are no business date column. How can I calculate it best?

 

Thank you!

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@stampgeorge Sample data would help tremendously. However, I would think that you could break the problem down along these lines:

Measure = 
  // This measure assumes RESORT[Name of Resort] is in the visual
  // Also, Statistics is related to RESORT based upon the name of the resort
  VAR __Rooms = MAX( 'RESORT'[Total Number of Rooms] )
  VAR __Days = COUNTROWS( DISTINCT( SELECTCOLUMNS( 'Statistics', "date", [business date] ) ) )
  VAR __Reservations = COUNTROWS( 'Statistics' )
  VAR __TotalRooms = __Rooms * __Days
  VAR __Result = DIVIDE( __Reservations, __TotalRooms )
RETURN
  __Result


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@stampgeorge Sample data would help tremendously. However, I would think that you could break the problem down along these lines:

Measure = 
  // This measure assumes RESORT[Name of Resort] is in the visual
  // Also, Statistics is related to RESORT based upon the name of the resort
  VAR __Rooms = MAX( 'RESORT'[Total Number of Rooms] )
  VAR __Days = COUNTROWS( DISTINCT( SELECTCOLUMNS( 'Statistics', "date", [business date] ) ) )
  VAR __Reservations = COUNTROWS( 'Statistics' )
  VAR __TotalRooms = __Rooms * __Days
  VAR __Result = DIVIDE( __Reservations, __TotalRooms )
RETURN
  __Result


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Thanks! This worked! The thing is, that it shows the correct % per resort, but it doesn't show the correct result for both the resorts. Is there a way to show the total occupancy in both resorts?

@stampgeorge First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.