Power BI Incremental Refresh for Distributor Sales: Late Invoices and Historical Corrections

A distributor-sales model covering several years and millions of rows is a classic incremental-refresh candidate: full refreshes take too long, so you reprocess only recent data and leave history in place. Configured naïvely, the model refreshes the last week each night and the dashboard is fast. Then the understatement starts.

Distribution data does not arrive neatly. A distributor submits a batch of invoices days after the sale, backdated to the actual transaction date. A regional office posts last month’s secondary sales a fortnight late. A correction reclassifies a return against a period that closed three weeks ago. If the incremental window only reprocesses the last few days by transaction date, none of these are ever picked up — the rows exist in the source, but the partition that should hold them is never refreshed again.

The result is a dashboard that looks current and is quietly wrong for recent-but-not-latest periods — exactly the periods demand planners and sales leaders rely on. The feature is not at fault; the configuration assumed well-behaved data that distribution never provides.

Power BI incremental refresh splits a table into partitions by a date column — typically one partition per day or month — and on each refresh only reprocesses the partitions inside a defined recent window, leaving older partitions untouched. That is what makes it fast: history is loaded once and frozen, and only the active window is re-queried.

The mechanism depends entirely on the date column you partition by and the width of the active window. If you partition by invoice transaction date and refresh the last 7 days, an invoice whose transaction date is 20 days ago falls in a frozen partition — even though it only reached the source yesterday. The platform is doing exactly what you configured; the configuration just did not account for data arriving after its own date.

This is the crux: incremental refresh reasons about when the data says it happened, not when it arrived. For sources where those two diverge — which is most distribution data — the configuration has to bridge the gap.

The first lever is the width of the refresh window, and it should be set to how late your data realistically arrives, not to a round number. If invoices routinely land up to three weeks after their transaction date, a refresh window of a few days is guaranteed to miss them; a window that reprocesses the last several weeks will catch them, at the cost of a little more refresh work.

Finding the right width is an empirical question: look at the gap between transaction date and load date across a few months of real data, and size the window to cover the bulk of the lateness — the 95th percentile of arrival lag, not the average. That keeps the refresh fast while catching almost all late invoices in the normal nightly run.

There is a genuine trade-off here. A wider window reprocesses more partitions every night, which costs time and capacity; a narrower one is faster but loses more late data. The right answer is the narrowest window that still covers how late your invoices actually arrive — which you can only know by measuring, not guessing.

Power BI’s "detect data changes" option sharpens this. Instead of blindly reprocessing every partition in the window, it checks a column — a last-modified timestamp — and only reprocesses partitions that actually changed since the last refresh. This lets you widen the window safely: you can cover a three-week lateness horizon without paying to reprocess three weeks of unchanged partitions every night, because only the ones with new or corrected rows are touched.

This depends on having a reliable last-modified column in the source that updates whenever a row is inserted or corrected. Many distribution systems have one; where they do not, the source team may need to add it, and that small change unlocks a much more efficient refresh. Without it, you are back to reprocessing the whole window regardless.

Used together, a window sized to real lateness plus detect-data-changes gives the best of both: late invoices are caught, and the nightly cost stays close to a narrow window because unchanged history is skipped.

Some corrections fall outside any reasonable rolling window — a reclassification against a quarter that closed two months ago, a restated prior year. No incremental window should be wide enough to cover those routinely; that would defeat the purpose. These need a targeted refresh instead: reprocess the specific historical partitions affected by the correction, on demand, rather than widening the standard window.

In practice this is a controlled operation — triggered when finance signals a restatement, refreshing the named partitions — so the historical figure updates without the nightly refresh carrying the cost of covering all of history every day. The XMLA endpoint and scripted partition refresh make this possible on Premium and Fabric capacities.

The discipline is to separate the two cases: normal lateness is handled by a correctly-sized rolling window with change detection, and exceptional corrections to closed periods are handled by targeted on-demand partition refreshes. Trying to make one rolling window cover both leads either to lost corrections or to a refresh that is no longer incremental.

Incremental refresh is the right tool for large distributor-sales models — it is the configuration that usually lets them down. Size the refresh window to the 95th-percentile arrival lag of your invoices rather than a round number, enable detect-data-changes so a wide window stays cheap, and handle restatements to closed periods with targeted partition refreshes rather than an ever-wider window. Do that and the dashboard stays both fast and correct for the recent periods planners actually use.

The diagnostic question is blunt: if a distributor posted an invoice today dated three weeks ago, would your model show it tomorrow? If you are not sure, your refresh window is probably narrower than your data is late — and the understatement is already happening quietly.