An independent two doctor practice on a standard PMS with a clearinghouse and an outside accountant. Nothing was missing from the software; the problem was that every number lived in its own report with its own date logic. Specifically:

• No shared date table. Production was pulled by procedure date, payments by deposit date, adjustments by posting date. Any comparison between them was apples to oranges.
• The denominator was never examined. The PMS reports a collection rate against net production, which is already reduced by write-offs nobody had reviewed. A 96% rate can sit on top of a badly negotiated fee schedule.
• Capacity was not modelled. Six operatories multiplied by opening hours is the entire manufacturing capacity of the business, and unlike most costs it cannot be scaled down on a slow Tuesday. No report expressed it.
• Diagnosed and completed procedures were never joined. Both sets existed in the ledger. The difference between them, which is the recoverable money, had no report.
• Insurance aging sat outside the picture. Money already earned, parked in claims, appeared in no summary the owner read.

Architecture

The one structural decision that makes everything else possible: a single date table, with every fact table joined to it by its own business date. Production hangs off procedure date, payments off deposit date, adjustments off posting date. Only then can the bridge be a single row of measures in one context.

Net collection rate, with the denominator in the open

The rate itself is trivial. The point is to compute and display the write-off separately, so the denominator is never quietly assumed:

-- Net collection rate: of what was collectible, how much we actually got
VAR GrossProduction = SUM( 'Procedures'[FeeAtFullSchedule] )
VAR ContractualAdj  = SUM( 'Adjustments'[ContractualWriteOff] )
VAR NetProduction   = GrossProduction - ContractualAdj
VAR Collected       = SUM( 'Payments'[Amount] )
RETURN
    DIVIDE( Collected, NetProduction )

Alongside it the adjustment rate against gross production, sliceable by plan. That single cut is what turns a payer conversation into a negotiation:

-- Adjustment rate against gross production (slice by plan)
DIVIDE(
    SUM( 'Adjustments'[ContractualWriteOff] ),
    SUM( 'Procedures'[FeeAtFullSchedule] )
)

Capacity as a denominator

Available chair time is not in any export, it is a parameter: operatories multiplied by open hours in the period, from the Google Sheets input. Utilization and production per chair hour both hang off it:

-- Available chair hours in the current context
VAR OpenHours  = SUMX( 'Calendar', RELATED( 'Hours'[OpenHoursOnDay] ) )
VAR Operatories = SELECTEDVALUE( 'Settings'[OperatoryCount] )
RETURN OpenHours * Operatories

-- Production per chair hour (works for a doctor or a hygiene column)
DIVIDE(
    [Gross production],
    SUMX( 'Appointments', 'Appointments'[ScheduledMinutes] ) / 60
)

Utilization uses booked minutes over available minutes in the same context, which is what lets the heatmap resolve to a single day and hour without a separate report.

Unscheduled treatment: a join, not a report

The recoverable list is the difference between two states of the same procedure table. Diagnosed, minus anything completed or currently on the schedule:

-- Value of treatment diagnosed and neither completed nor scheduled
CALCULATE(
    SUM( 'Procedures'[FeeAtFullSchedule] ),
    'Procedures'[Status] = "Diagnosed",
    ISBLANK( 'Procedures'[CompletedDate] ),
    ISBLANK( 'Procedures'[ScheduledDate] )
)

Priority is where the model earns its keep. Remaining annual benefit comes from plan maximums minus benefit already used in the current benefit year, and it decays to zero on the plan reset date:

-- Remaining annual benefit, current benefit year
VAR PlanMax  = SELECTEDVALUE( 'Plans'[AnnualMaximum] )
VAR UsedYTD  =
    CALCULATE(
        SUM( 'Payments'[InsurancePortion] ),
        DATESBETWEEN(
            'Calendar'[Date],
            [Benefit year start],
            MAX( 'Calendar'[Date] )
        )
    )
RETURN MAX( PlanMax - UsedYTD, 0 )

Sorting the list by remaining benefit and days pending, rather than by treatment value alone, is what makes the front desk trust it. The expensive implant with no benefit left is a slower conversation than the crown with $900 expiring in six weeks.

Recall as a set of buckets

Recall status is derived, not stored: last hygiene visit plus the interval from the input sheet, compared to today. Buckets, a reactivation rate per bucket taken from the practice's own history, and a recoverable number:

Refresh path

Scheduled PMS reports land in a folder, the claims file lands next to them, Power Query reads the folder rather than named files. One "Refresh all" and the whole month is current, including anything the office manager changed in the input sheet.