A full schedule is not a full bank account
The collection bridge as a row of measures
In any business that sells booked time, work performed and money received are two different numbers, and the distance between them is made of specific, nameable things. Most reporting shows you both ends and none of the middle. Here is the method that makes every step in between visible.
One date table, every fact joined by its own business date, and a bridge computed as a sequence of measures rather than assembled by hand each month. Below: the structure, the DAX, and why the denominator of a collection rate deserves more attention than the rate.
01 Two numbers with a gap between them
A clinic, a studio, a workshop, a repair business. The software reports what you produced this month and what you collected this month, on separate screens, computed over different date ranges. Both numbers are correct. Neither explains the other.
The distance between them is not a mystery, it is a list. Contractual write-offs against agreed rate cards. Courtesy discounts nobody logged as a decision. Balances that were never chased. Invoices sitting with an insurer, a platform or a corporate client who pays in 60 days. Every one of those is already in your data, and none of them appear on a summary anybody reads.
02 The method: one bridge, named lines
Take gross production at your full rate card, then subtract each reduction as its own line, in the order it actually happens, until you reach money in the account. Switch periods to see how the shape changes:
Two lines carry most of the surprise. Contractual write-offs are almost always bigger than the team assumes, and once you can split them by payer, plan or platform, a vague sense that "this contract feels bad" becomes a number you can take into a renegotiation. Invoices still in aging are money you have already earned; the moment that becomes a single visible figure instead of a pile of paperwork, somebody owns it.
03 The rate everyone quotes, and the one that matters
Most software reports a collection rate: what you collected against what was collectible. It is a comfortable number, and it can be 96% while you are quietly leaving serious money behind, because the thing you measured against was already reduced by write-offs nobody reviewed.
04 If you sell booked time, measure the time
A service business with rooms, chairs, bays or trucks has a hard capacity ceiling, and unlike most costs it cannot be scaled down on a slow Tuesday. Revenue per available hour is therefore the honest productivity number, and it is the only way to compare two people, two locations or two service lines without arguing about who works harder.
Utilization by day and hour, rather than as a monthly average, is what makes it actionable. An average tells you the month was 78% full. A grid tells you Friday afternoons are 40% full every single week, which is a thing you can actually fix.
01 Why the bridge usually cannot be built
The blocker is almost never missing data, it is date semantics. Production is dated by when work was performed. Payments are dated by when money landed. Write-offs are dated by when someone posted them. Aging is dated by submission. Four grains, four calendars, and any naive comparison between them is wrong.
So the first move is structural: one date table, and every fact table joined to it by its own business date. Only then can a bridge be a sequence of measures evaluated in one filter context.
| Fact table | Joined to the date table by |
|---|---|
| Services performed | ServiceDate |
| Write-offs and discounts | PostingDate |
| Payments received | DepositDate |
| Open invoices | SubmittedDate |
| Bookings and capacity | SlotDate |
02 The bridge as measures
Each line is its own measure, so the bridge can be sliced by payer, service line, location or provider without rebuilding anything:
-- Gross production at the full rate card Gross production = SUM( 'Services'[FeeAtFullRate] ) -- What we agreed not to charge Contractual write-off = SUM( 'Adjustments'[ContractualAmount] ) -- What we chose not to charge Discretionary discount = SUM( 'Adjustments'[CourtesyAmount] ) -- What is collectible after both Net production = [Gross production] - [Contractual write-off] - [Discretionary discount]
Splitting the write-off into contractual and discretionary is not cosmetic. The first is a contract you signed and can renegotiate; the second is a decision someone made this week. They belong to different people and different conversations.
03 Collection rate, with the denominator exposed
Report both, always together. The rate answers "did we chase what we could", the write-off percentage answers "was it worth chasing":
-- Of what was collectible, how much actually arrived Net collection rate = DIVIDE( [Collected], [Net production] ) -- How much of the full rate card we never billed Write-off rate = DIVIDE( [Contractual write-off] + [Discretionary discount], [Gross production] )
04 Money earned and not yet received
Aging is a separate measure over open invoices, bucketed by age. The buckets matter more than the total, because recovery probability falls off a cliff somewhere in your data and you want to know where:
-- Open balance by age bucket Open balance = VAR AsOf = MAX( 'Calendar'[Date] ) RETURN SUMX( FILTER( 'Invoices', ISBLANK( 'Invoices'[ClosedDate] ) && 'Invoices'[SubmittedDate] <= AsOf ), 'Invoices'[OpenAmount] )
05 Capacity is a parameter, not an export
No system exports "hours we could have sold". It is rooms multiplied by open hours, and it belongs in an input sheet the operations manager controls:
-- Available hours in the current context Available hours = VAR OpenHours = SUMX( 'Calendar', RELATED( 'Hours'[OpenHoursOnDay] ) ) VAR Units = SELECTEDVALUE( 'Settings'[ServiceUnitCount] ) RETURN OpenHours * Units -- The productivity number that survives comparison Revenue per available hour = DIVIDE( [Net production], [Available hours] )
Utilization is booked hours over available hours in the same context, which is what lets one measure resolve to a month, a weekday or a single hour of the day with no extra report.
06 Why it keeps working
Rate cards, plan participation, open hours and recall intervals live in an input sheet, not in formulas. When a contract changes, someone edits a cell and the next refresh recomputes the whole bridge, including every historical period, on the new rules. That is the difference between a report you rebuild each month and a model you refresh.
Let's build your bridge on your own numbers
In a free review we take your last quarter of exports and itemize the distance between what you produced and what you collected. No obligation.