5 DAX Patterns Every Hospitality Analyst Should Know

After years working across Swiss 4 and 5-star hotels — from front office to revenue management — I've built a lot of Power BI dashboards. The same questions come up every time: What's our occupancy tonight? How are we tracking against last year? Where is RevPAR leaking?

These five DAX patterns answer exactly those questions. They're the ones I reach for on every hospitality project.

1. Occupancy Rate

This is the foundation of every hotel dashboard. Occupancy is simply rooms sold divided by rooms available — but in DAX you need to handle dates carefully to avoid dividing by zero on days with no availability.

-- Occupancy Rate %
Occupancy Rate =
DIVIDE(
    SUM( Reservations[RoomNights] ),
    SUM( RoomInventory[AvailableRooms] ),
    0
)

Tip: Build your RoomInventory table separately with one row per date per room type. This gives you flexible filtering by season, room category, or property.

2. Year-over-Year Revenue Comparison

YoY comparisons are the most-requested metric in every hotel boardroom. Use SAMEPERIODLASTYEAR wrapped in CALCULATE to shift context cleanly.

-- Revenue Last Year (same period)
Revenue LY =
CALCULATE(
    SUM( Reservations[Revenue] ),
    SAMEPERIODLASTYEAR( 'Date'[Date] )
)

-- YoY Growth %
Revenue YoY % =
DIVIDE(
    SUM( Reservations[Revenue] ) - [Revenue LY],
    [Revenue LY],
    BLANK()
)

3. RevPAR (Revenue Per Available Room)

RevPAR is the single most important KPI in hotel revenue management. It combines both occupancy and rate into one number, making it ideal for tracking overall performance.

-- RevPAR
RevPAR =
DIVIDE(
    SUM( Reservations[Revenue] ),
    SUM( RoomInventory[AvailableRooms] ),
    0
)

The difference from ADR (Average Daily Rate) is subtle but important: RevPAR penalises empty rooms, ADR does not. Always show both side by side.

4. Lead Time Analysis

Knowing how far in advance guests book is critical for pricing strategy. A 2-night leisure booking made 90 days out is very different from a same-day walk-in. This measure calculates average lead time dynamically based on your slicer selection.

-- Average Booking Lead Time (days)
Avg Lead Time =
AVERAGEX(
    Reservations,
    DATEDIFF(
        Reservations[BookingDate],
        Reservations[ArrivalDate],
        DAY
    )
)

Use this in a histogram visual to spot your booking window distribution. Most Swiss luxury hotels see a bimodal pattern: last-minute and 60–90 day windows.

5. Running Total with Period Reset

Monthly running totals are essential for tracking pace against budget. The trick is resetting at the start of each month — a common pitfall for analysts new to DAX.

-- Month-to-date Revenue
Revenue MTD =
CALCULATE(
    SUM( Reservations[Revenue] ),
    DATESMTD( 'Date'[Date] )
)

-- Year-to-date Revenue
Revenue YTD =
CALCULATE(
    SUM( Reservations[Revenue] ),
    DATESYTD( 'Date'[Date] )
)

Putting it together

These five patterns cover roughly 80% of the analytical questions I get from hotel operations teams. Build them as reusable measures in a dedicated _Measures table, and you'll have a solid foundation for any hospitality dashboard.

In a future post I'll cover dynamic benchmarking — comparing individual properties against a portfolio average using ALL() and REMOVEFILTERS().

← Back to Blog