A technical walkthrough of Resort Operations Performance

Multi-property operators do not need another pretty gauge cluster.

They need occupancy, ADR, and RevPAR that still reconcile to the PMS at 6 a.m. — when finance, revenue, and the front desk are all looking at the same morning.

Behind the Resort Operations Performance dashboard — the crop below is the same language on the full board (occupancy, ADR, RevPAR, rooms revenue, guest NPS, booking funnel, tonight's room status, 14-day pickup) — is a deliberate multi-property data architecture. Gauges are the last mile. Room-night grain, inventory definition, and one definition of RevPAR are what keep every property scorecard honest.

Open live dashboard ↗  ·  Work Showcase card

A note before you continue: this is a long-form technical article—not a one-page overview. We walk through grain, KPI definitions, pipeline context, and a substantial DAX starter the way we actually shape multi-property models. If you read it through, you will leave with concrete patterns you can apply or evaluate on a real PMS-backed stack—not just a polished screenshot.

What the architecture is doing

• Star schema for resort operations — fact tables for room nights (sold + available capacity), reservations / pipeline, room inventory status, pickup (on-the-books by lead day), guest feedback, and ancillary revenue; dimensions for Property, Date, Segment, Room Type, Channel, and Rate Plan.
• Semantic model with hotel-native DAX — Occupancy, ADR, and RevPAR are three faces of the same room-night math, not three disconnected spreadsheets. Rooms revenue, GOPPAR, and NPS reuse the same property and date filters.
• Pipelines from systems that run the resort — PMS (live inventory and stays), CRS / booking engine (pipeline), channel manager, housekeeping / room status, spa and F&B POS or outlet feeds, guest survey / CRM — so MTD and “tonight” stay current without export theater.
• Portfolio + property grain — the same measures roll from a single key to the multi-property portfolio without redefining RevPAR per asset.

Below is a DAX starter you can paste into a model that follows that layout. It goes a bit deeper than a KPI-only post: core hospitality math, funnel stages, tonight's house status, and pickup pace patterns. It is still intentionally incomplete — foundation and naming discipline, not every RLS rule or yield-management edge case from a production deployment.

How the data needs to be laid out

These measures assume a classic star with single-direction relationships from facts to dimensions. In hospitality, grain is everything: get room night vs reservation vs folio wrong and occupancy will never match the PMS.

• FactRoomNight — one row per sellable room night (or aggregated day × property × room type). Required: DateKey, PropertyKey, RoomTypeKey, RoomsAvailable (capacity for that night after OOO policy), RoomsSold (occupied / sold room nights), RoomsRevenue (rooms only — not F&B). Optional: SegmentKey, ChannelKey, RatePlanKey if you allocate sold nights that way.
• FactReservation — pipeline / funnel grain: one row per reservation (or quote) with stage. Required: ReservationKey, PropertyKey, CreateDateKey, ArrivalDateKey, Stage (Inquiry, Confirmed, Guaranteed, ArrivalThisWeek, InHouse, …), RoomNights, optional lead counts from CRM.
• FactRoomStatusDaily — tonight house snapshot. Required: SnapshotDateKey, PropertyKey, RoomKey (or pre-aggregated counts), Status: Occupied, VacantClean, VacantDirty, OOO, Arrival, Departure. Optional: HkMinutesInQueue, IsLateCheckout, IsUnassignedArrival.
• FactPickup — on-the-books (OTB) room nights by stay date and as-of date (lead day D+1…D+14). Required: StayDateKey, AsOfDateKey (or LeadDays), PropertyKey, OtbRoomNights. Prefer OtbRoomNightsLY at extract time for same-week LY.
• FactGuestSurvey — Guest NPS / CSAT. Required: ResponseDateKey, PropertyKey, NpsScore (0–10 classic scale or documented alternative), optional ComplaintFlag, StayDateKey.
• FactAncillary — F&B, spa, golf, events by day/property. Required: DateKey, PropertyKey, AncillaryCategory, RevenueAmount.
• DimDate — contiguous calendar; mark as date table. Include week helpers for LY pickup alignment.
• DimProperty — PropertyKey, PropertyName, key inventory, optional budget targets (occ target, ADR budget, RevPAR budget).
• DimSegment — Leisure, Corporate, Group (map PMS market codes, not free text).
• DimRoomType / DimChannel / DimRatePlan — stable keys; never join on display names alone.

Relationships (examples): FactRoomNight[DateKey] → DimDate[DateKey], FactRoomNight[PropertyKey] → DimProperty[PropertyKey], same pattern for status, pickup, survey, and ancillary facts. Prefer one date table; use USERELATIONSHIP only when a second date role is deliberate (arrival vs stay). Keep fact-to-fact relationships out unless you have a bridge you can defend.

Hospitality KPI definitions (so the gauges mean one thing)

Document these on the model, not only in a slide deck. The crop uses the industry-standard relationships:

• Occupancy = Room nights sold ÷ Room nights available (same property + date filter).
• ADR (Average Daily Rate) = Rooms revenue ÷ Room nights sold.
• RevPAR = Rooms revenue ÷ Room nights available or Occupancy × ADR (equivalent when sold/available sets match).
• GOPPAR (optional) = Gross operating profit ÷ available rooms — needs a real P&L fact; do not invent it from rooms revenue alone.
• Available rooms must use one OOO policy portfolio-wide (physical keys minus OOO/maintenance, or sellable capacity — pick one and stick to it).

DAX starter — core rooms KPIs

Create a measures table (e.g. _Measures) and paste. Names match the crop: Occupancy, ADR, RevPAR, Rooms Revenue, Guest NPS.

// --- Core rooms math (FactRoomNight grain) ---
// RoomsAvailable = sellable capacity after your OOO policy (document it).
// RoomsSold = occupied / sold room nights for the same grain.
// RoomsRevenue = rooms only (exclude F&B, spa, packages unbundled elsewhere).

Rooms Available :=
SUM ( FactRoomNight[RoomsAvailable] )

Rooms Sold :=
SUM ( FactRoomNight[RoomsSold] )

Rooms Revenue :=
SUM ( FactRoomNight[RoomsRevenue] )

// Occupancy — gauge center (e.g. 84.6%)
Occupancy :=
DIVIDE ( [Rooms Sold], [Rooms Available] )

// ADR — rooms revenue per sold night (e.g. $312)
ADR :=
DIVIDE ( [Rooms Revenue], [Rooms Sold] )

// RevPAR — rooms revenue per available night (e.g. $264)
// Equivalent to [Occupancy] * [ADR] when sold/available are consistent
RevPAR :=
DIVIDE ( [Rooms Revenue], [Rooms Available] )

// Prior MTD for "vs prior MTD" callouts on the gauges
// Requires DimDate marked as the model date table
Rooms Revenue Prior MTD :=
CALCULATE ( [Rooms Revenue], DATEADD ( DimDate[Date], -1, MONTH ) )

Occupancy Prior MTD :=
CALCULATE ( [Occupancy], DATEADD ( DimDate[Date], -1, MONTH ) )

ADR Prior MTD :=
CALCULATE ( [ADR], DATEADD ( DimDate[Date], -1, MONTH ) )

RevPAR Prior MTD :=
CALCULATE ( [RevPAR], DATEADD ( DimDate[Date], -1, MONTH ) )

Occupancy vs Prior MTD pts :=
[Occupancy] - [Occupancy Prior MTD]

ADR vs Prior MTD :=
[ADR] - [ADR Prior MTD]

RevPAR vs Prior MTD % :=
DIVIDE ( [RevPAR] - [RevPAR Prior MTD], [RevPAR Prior MTD] )

// Optional budgets from DimProperty (SELECTEDVALUE under one property)
// Occupancy Target := SELECTEDVALUE ( DimProperty[OccupancyTarget] )
// ADR Budget := SELECTEDVALUE ( DimProperty[AdrBudget] )
// RevPAR Budget := SELECTEDVALUE ( DimProperty[RevparBudget] )

// --- Guest NPS (FactGuestSurvey) ---
// Classic 0–10: Promoters 9–10, Passives 7–8, Detractors 0–6

Survey Responses :=
COUNTROWS ( FactGuestSurvey )

Promoters :=
CALCULATE (
    COUNTROWS ( FactGuestSurvey ),
    FactGuestSurvey[NpsScore] >= 9
)

Detractors :=
CALCULATE (
    COUNTROWS ( FactGuestSurvey ),
    FactGuestSurvey[NpsScore] <= 6
)

Guest NPS :=
VAR n = [Survey Responses]
RETURN
    DIVIDE ( [Promoters] - [Detractors], n ) * 100

// --- Ancillary (F&B + spa tile on the crop) ---
Ancillary Revenue :=
SUM ( FactAncillary[RevenueAmount] )

FB And Spa Revenue :=
CALCULATE (
    [Ancillary Revenue],
    FactAncillary[AncillaryCategory] IN { "F&B", "Restaurant", "Spa" }
)

// --- GOPPAR starter (only if operating profit exists in the model) ---
// FactProfit[DateKey], [PropertyKey], [GrossOperatingProfit]
// GOPPAR := DIVIDE ( SUM ( FactProfit[GrossOperatingProfit] ), [Rooms Available] )

DAX starter — booking funnel (next 30 days)

The crop funnel is a stage ladder for the portfolio, not a web-analytics funnel. Each bar is a count (or room nights) at that stage; conversion % is stage ÷ top-of-funnel in the same filter context (next 30 days).

// --- Booking funnel (FactReservation) ---
// Align "Next 30 days" with the stay/arrival window used on the board.

Funnel As Of :=
MAX ( DimDate[Date] )

Funnel End :=
[Funnel As Of] + 30

// Pattern: filter FactReservation by stage + arrival window.
// Wire ArrivalDateKey -> DimDate (or TREATAS) so DATESBETWEEN works.

Funnel Inquiries :=
CALCULATE (
    DISTINCTCOUNT ( FactReservation[ReservationKey] ),
    FactReservation[Stage] IN { "Inquiry", "Quote" },
    DATESBETWEEN ( DimDate[Date], [Funnel As Of], [Funnel End] )
)

Funnel Confirmed :=
CALCULATE (
    DISTINCTCOUNT ( FactReservation[ReservationKey] ),
    FactReservation[Stage] IN { "Confirmed", "ConfirmedBooking" },
    DATESBETWEEN ( DimDate[Date], [Funnel As Of], [Funnel End] )
)

Funnel Guaranteed :=
CALCULATE (
    DISTINCTCOUNT ( FactReservation[ReservationKey] ),
    FactReservation[Stage] IN { "Guaranteed", "GuaranteedCC" },
    DATESBETWEEN ( DimDate[Date], [Funnel As Of], [Funnel End] )
)

Funnel Arrivals This Week :=
CALCULATE (
    DISTINCTCOUNT ( FactReservation[ReservationKey] ),
    FactReservation[Stage] = "ArrivalThisWeek",
    DATESBETWEEN ( DimDate[Date], [Funnel As Of], [Funnel As Of] + 7 )
)

Funnel Checked In Tonight :=
CALCULATE (
    DISTINCTCOUNT ( FactReservation[ReservationKey] ),
    FactReservation[Stage] IN { "InHouse", "CheckedIn" }
)

// Conversion % for bar labels (stage / inquiries)
Funnel Confirmed % :=
DIVIDE ( [Funnel Confirmed], [Funnel Inquiries] )

Funnel Guaranteed % :=
DIVIDE ( [Funnel Guaranteed], [Funnel Inquiries] )

// Side labels: CALCULATE ( SUM ( FactReservation[RoomNights] ), ... same stage filters )

DAX starter — tonight room status

Tonight's board is an inventory snapshot, not a stay-date fact. Snapshot as-of must match business “tonight,” and OOO must use the same definition as Rooms Available on the MTD gauges or the house will never foot.

// --- Tonight room status (FactRoomStatusDaily) ---
// Filter to SnapshotDateKey = report as-of / property business date.

Rooms Occupied :=
CALCULATE (
    COUNTROWS ( FactRoomStatusDaily ),
    FactRoomStatusDaily[Status] = "Occupied"
)

Rooms Vacant Clean :=
CALCULATE (
    COUNTROWS ( FactRoomStatusDaily ),
    FactRoomStatusDaily[Status] = "VacantClean"
)

Rooms Vacant Dirty :=
CALCULATE (
    COUNTROWS ( FactRoomStatusDaily ),
    FactRoomStatusDaily[Status] = "VacantDirty"
)

Rooms OOO :=
CALCULATE (
    COUNTROWS ( FactRoomStatusDaily ),
    FactRoomStatusDaily[Status] IN { "OOO", "Maintenance" }
)

Arrivals Tonight :=
CALCULATE (
    COUNTROWS ( FactRoomStatusDaily ),
    FactRoomStatusDaily[Status] = "Arrival"
)

Departures Tonight :=
CALCULATE (
    COUNTROWS ( FactRoomStatusDaily ),
    FactRoomStatusDaily[Status] = "Departure"
)

// % of keys occupied tonight (may differ from MTD Occupancy)
Tonight Occupied % of Keys :=
DIVIDE (
    [Rooms Occupied],
    CALCULATE ( COUNTROWS ( FactRoomStatusDaily ) )
)

// HK pressure
HK Queue Avg Minutes :=
CALCULATE (
    AVERAGE ( FactRoomStatusDaily[HkMinutesInQueue] ),
    FactRoomStatusDaily[Status] = "VacantDirty"
)

Arrivals Not Yet Assigned :=
CALCULATE (
    COUNTROWS ( FactRoomStatusDaily ),
    FactRoomStatusDaily[Status] = "Arrival",
    FactRoomStatusDaily[IsUnassignedArrival] = TRUE ()
)

Late Checkouts :=
CALCULATE (
    COUNTROWS ( FactRoomStatusDaily ),
    FactRoomStatusDaily[IsLateCheckout] = TRUE ()
)

DAX starter — 14-day pickup pace

Pickup compares on-the-books room nights for stay dates D+1…D+14 against the same lead structure last year. Bars = OTB; line = same week LY. Lead day must come from a single as-of timestamp per refresh.

// --- 14-day pickup (FactPickup) ---
// FactPickup[StayDateKey], [AsOfDateKey], [PropertyKey], [OtbRoomNights]
// LeadDays = StayDate - AsOfDate (1..14 on this visual)

OTB Room Nights :=
SUM ( FactPickup[OtbRoomNights] )

// Prefer LY captured at extract time on the same grain
OTB Room Nights LY :=
SUM ( FactPickup[OtbRoomNightsLY] )

// Axis for the column chart: LeadDays (1..14)
// Values: [OTB Room Nights] with AsOf fixed to last refresh

Pickup vs LY :=
[OTB Room Nights] - [OTB Room Nights LY]

Pickup vs LY % :=
DIVIDE ( [Pickup vs LY], [OTB Room Nights LY] )

// Pace insight: bars above the LY line = ahead of last year for that lead day

Property scorecards, segment mix, guest ops SLAs

The full board (beyond the crop) reuses the same core measures in different shapes — no second definition of RevPAR:

• Property scorecards — Property on cards/rows; values = Occupancy, RevPAR, Guest NPS, plus a health band from thresholds.
• Segment & ancillary — rooms revenue by DimSegment; ancillary bars from FactAncillary (restaurants, spa, golf, events).
• Guest ops SLAs — check-in under 10 minutes, housekeeping on-time %, CSAT, complaint %, repeat book — usually PMS timestamps + survey flags, not FactRoomNight alone.

// --- Property scorecard status (example thresholds — tune per brand) ---
Property Health Band :=
VAR occ = [Occupancy]
VAR nps = [Guest NPS]
RETURN
    SWITCH (
        TRUE (),
        occ >= 0.88 && nps >= 72, "Strong",
        occ >= 0.82 && nps >= 68, "On plan",
        occ >= 0.75, "Shoulder",
        "Watch"
    )

// Segment share of rooms revenue (stacked bar)
Rooms Revenue Segment % :=
DIVIDE (
    [Rooms Revenue],
    CALCULATE ( [Rooms Revenue], ALLSELECTED ( DimSegment ) )
)

What this starter deliberately leaves out

On purpose — so the post stays useful without pretending one paste replaces a full multi-property build:

• Full relationship diagram, role-playing dates (arrival vs stay vs snapshot), and bi-directional pitfalls
• Package breakdowns, group blocks, wash forecasts, and overbooking / yield controls
• Channel manager parity, BAR vs negotiated rates, and net ADR after commission
• Housekeeping task-level labor standards and room-type cleaning minutes
• Multi-currency portfolios, owner vs managed P&L, and GOPPAR cost allocation
• Row-level security by property/region and deployment (gateway, incremental refresh, hybrid tables)

Those are where resort models usually get customized. The starter above is the shared skeleton: same room-night math, same inventory definition, same “one RevPAR” on every gauge and every property card.

See the full board

Open the live Resort Operations Performance sample in Power BI Service — same multi-property board this article is built around:

Open live dashboard ↗  ·  Work Showcase card

Related industry boards also live on the Work Showcase. Analytics projects start from $1,200 — see Data Analytics or open a project inquiry when you want this modeled on your PMS, CRS, and guest-experience stack.