Power BI Marketing Model That Ties to the General Ledger

Finance will not accept a marketing dashboard whose spend figure disagrees with the invoice ledger, and most of them do. The difference is nearly always the same four adjustments: agency fees, platform rebates, credits, and the date of the FX rate. None are controversial once written down. This build reconciles to the general ledger on an agreed definition of net media cost and prints that reconciliation as a page in the report rather than burying it in a footnote nobody opens. The semantic model: A star schema, not a flat export. Conformed date, campaign, channel, entity and currency dimensions; separate fact tables at daily spend grain and at conversion grain. Measures written once in DAX with a single definition each, so gross spend, net media cost and contribution never exist in two competing versions across two workbooks. Time intelligence built against a fiscal calendar starting in April, which the automatic date table will not handle correctly for a Japanese entity and which quietly misstates every year-to-date figure until someone notices in Q3. In Tableau the same model ships as a published data source with the calculations centralised there rather than copied into individual workbooks. Multi-entity and currency: Spend booked in JPY, USD and EUR converted on a rate policy you choose and I document — transaction-date rate for management reporting, month-end for anything that has to tie to statutory accounts. Row-level security by entity and brand, so a country manager opens the same report and sees their own figures. Where a Japanese subsidiary reports upward to a foreign parent, the model carries both views and states which is authoritative for each figure, because the alternative is two teams presenting different numbers for the same quarter. Refresh and governance: Incremental refresh partitioned by date with a rolling restatement window, so a month closed three cycles ago is not re-queried nightly while a month still moving is. On-premises gateway where a source demands it, service principal authentication rather than someone's personal account, deployment across development, test and production. Refresh failures alert to a channel naming the failing partition, because a silently stale dashboard causes more damage than a visibly broken one. Out of scope: Data acquisition. This assumes spend and conversion data already lands somewhere queryable; building that path is separate work. Subject areas outside marketing, even where the same tenant hosts them. Licensing, Fabric capacity purchase and tenant administration. Adopting an inherited model without rewriting it — I will not certify the numbers coming out of a workbook carrying two hundred calculated columns I did not write. Where I say no: If nobody in finance will sit in the definition sessions, the project should not start. The reconciliation is an agreement between two departments, and a model built with one department absent gets rewritten within a year. I also decline builds meant to replace an existing report that nobody intends to retire. Two live versions of the same figure diverge, and in every case I have watched, the older one wins the argument. Who it is for: Companies where the marketing number now has an audience outside marketing — a board pack, a parent company review, an investor update, a regulator. Retail energy suppliers reporting acquisition cost per switched contract against a back-office confirmation file, and multi-location operators reporting cost per booked appointment by site, are the two shapes I have built most often. Handover: The model file and published data source, a measure dictionary with one sentence per definition, the reconciliation page, refresh and security configuration documented, and two sessions with the person taking ownership.