An internal business report. The requirement read 'connect our existing data so we can see it.' It sounds light; most of the work turned out to be arguing over definitions.
Where the Data Lives
Six tables across three systems: project ledger, contracts, payments received, personnel, timesheets, plus a dictionary table. Two departments also send a monthly Excel each — one for subcontracting costs, one for regional adjustments — filed into a fixed shared-drive directory, filenames carrying the month.
V1 stuffed all six tables straight into Power BI, drew the relationships, produced the charts — and the numbers disagreed. Marketing said 87 completed this month; the project department said 91; finance reported 84. Nobody had computed wrong; each defined 'completed' differently: one counted contract signing, one counted acceptance sign-off, one counted cash received.
Define Terms First, Build the Model Second
We stopped and spent half a day writing each metric's definition as one sentence, posted on the report's front page under 'definitions.' 'Completed' was ultimately defined by the acceptance date, with two parallel metrics alongside — 'contracts signed' and 'payments received' — each read on its own, none substituting for another.
Only then did the model come together. A daily job at 2 a.m. JOINs the six tables into one wide table, 23 fields, written to report_daily. Power BI connects only to that table, never touching the production databases, so report queries can't drag the live systems down.
The two Excel files bypass the database entirely; Power BI reads them from the folder, always taking the latest month. Missing-file months get a yellow line on the report — 'regional adjustment not yet received' — never silently treated as zero. That lesson came from a maintenance contract, and this time it got used.
How the Definitions Page Got Written
'Completed counts by acceptance date' — that single sentence took half a day of arguing before it was written. Marketing wanted contract signing, because their monthly target is signed value; the project department wanted acceptance, because unaccepted work isn't done; finance wanted payments, because money not in the account doesn't get reported. All three were right — each was just looking at a different table.
In the end we didn't pick one 'correct answer.' We built all three as independent metrics with their own names, and the front-page definitions document states for each which table it comes from, which date field it uses, and whether returns are included. Whoever looks at a chart reads their own line. That page later got printed by the client and pinned to the finance office wall — it has outlived every chart we made.
The Yellow Missing-File Line
The 'regional adjustment not yet received' line came straight from that maintenance-contract lesson. In V1 we silently treated it as zero; the report still produced numbers, just missing a piece. The client took it into a meeting, got asked 'why is North China so low this month,' and spent ages discovering the Excel hadn't arrived. After we made the gap a loud yellow marker, that meeting-room embarrassment never recurred.
That line later spawned a small feature: months with missing files get an extra remark row on export. Finance said it let her tell 'genuinely zero' from 'no data' at a glance during reconciliation.
Three Screens Later
Six months after launch the client added a screen themselves — a per-person timesheet distribution, fed by the same report_daily, model untouched. We weren't involved; a Power BI-capable colleague on their side dragged it together. That's probably the ideal ending: a clean wide table, definitions written out — the rest they can do themselves.
The definitions document now runs 11 lines, three more than V1, all details added later that nobody dares delete. Line 11 was added in 2022: 'cross-month returns count in the original month.' The page has been reprinted once since, with the font bumped up a size — easier to read on the wall, they said.
The Report Is Three Screens
Screen one, overview: six cards, one trend line. Screen two, drill-down by region. Screen three, the detail table, exportable.
The client originally wanted a dozen-plus charts on one screen; we didn't build that. Three screens proved enough, and the third screen's export gets clicked most — most people just want the numbers for their weekly reports.
Launch
Delivered June 11. The first week saw four changes, all definitional: one region's history had to follow the old formula, one subcontracting cost crossed months, and two disabled dictionary entries had to be excluded. Each got its annotation on that definitions page.
Once, someone on the client's side pointed at that front-page sentence and joked: this line is worth more than every chart behind it. I didn't argue.
