Power BI Inventory Control Tower
A stock control dashboard people actually open on Monday morning
By Renjith ·
Prerequisites
- Power BI Desktop
- Working knowledge of DAX filter context
- Access to transactional inventory data (or the sample dataset)
The problem
The business already had eleven inventory reports. Nobody used them.
They were built the way most dashboards are built — someone asked "what do you want to see?", the answers were turned into visuals, and the result was a page of accurate charts that answered no particular question. Accurate and useless are entirely compatible.
The real problem was never the visuals. It was that nobody had written down the decisions the report was supposed to support.
What was built
A single control tower organised around four weekly decisions rather than around the data model:
- What do we reorder this week? — cover days against lead time, ranked by stockout risk × value.
- What is stuck? — ageing bands with capital tied up, sorted by cost of holding.
- Where is the forecast wrong? — bias and absolute error by category, so the planning conversation starts from evidence.
- What changed since last week? — an explicit delta view, because "what moved" is the question people actually arrive with.
Underneath is a star schema with a proper date table, calculation groups for time intelligence, and aggregation tables sized to keep every page under a second on 40 million transaction rows.
How it works
The measure that made it useful#
Cover days — how long current stock lasts at recent demand — is the number the whole reorder decision turns on. Getting it right in DAX is mostly about being careful with which dates you average over.
Cover Days.daxdaxCover Days =
VAR RecentDailyDemand =
DIVIDE (
CALCULATE (
[Units Shipped],
DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -28, DAY )
),
28
)
VAR CurrentStock = [Closing Stock Units]
RETURN
DIVIDE ( CurrentStock, RecentDailyDemand )
Two deliberate choices here. A 28-day window rather than a calendar month, so the number is not distorted by month length. And DIVIDE rather than /, so a product with no recent demand returns blank instead of infinity — which matters because infinity sorts to the top of "highest cover" and buries the real answers.
Making it fast#
The naïve model ran the reorder page in 8.4 seconds. Three changes took it to 0.6:
| Change | Effect |
|---|---|
| Aggregation table at day/product/location | 8.4s → 2.1s |
| Removing bidirectional filters between fact and supplier | 2.1s → 1.3s |
Replacing a nested FILTER over the fact table with KEEPFILTERS |
1.3s → 0.6s |
The bidirectional filter tax
Bidirectional relationships are convenient and they force the engine to do materially more work per query. In this model, removing two of them was worth more than any DAX rewrite. Reach for them last, not first.
Why it gets opened#
The delta page. Every other page answers "what is the state of things", which is a question people ask once. "What changed since last week" is a question they ask every week, and it is the page that turned the report into a habit.
Build steps
- 1
Write the decisions down first
Four sentences, agreed with the person who will use the report. Every visual that does not serve one of them gets cut. This step removed more work than it created.
- 2
Build the star schema
Fact table at transaction grain, conformed dimensions for product, location, supplier and date. No snowflaking, no bidirectional filters.
- 3
Add a real date table
Marked as a date table, covering full years, with fiscal columns. Almost every time-intelligence bug traces back to skipping this.
- 4
Write base measures once
A small set of base measures, then calculation groups for period comparison. Twelve measures instead of a hundred and twenty.
- 5
Add aggregations
A daily aggregation table at product-location grain, with the detail table in DirectQuery behind it. Most interactions never touch the detail.
- 6
Test with Performance Analyzer
Every page measured, every query over 500ms investigated. Two visuals were redesigned because their DAX could not be made fast.
Lessons learned
Write the decisions before the visuals. Four sentences on a whiteboard removed more work from this project than any technical choice.
Model shape beats DAX cleverness. The largest performance wins came from relationships and aggregations, not from rewriting measures.
DIVIDE everywhere. Infinity and divide-by-zero errors are the most common way a Power BI report quietly stops being trusted.
A delta view creates the habit. State pages get looked at once; change pages get looked at weekly.
Limitations
The aggregation strategy assumes queries are usually at day grain or coarser. A workload that routinely drills to transaction level will not see these gains.
Forecast accuracy pages assume forecasts are stored with their as-of date. Many organisations overwrite forecasts in place, which makes bias measurement impossible after the fact.
Row-level security is not implemented in the sample model and will change the performance profile when added.
Future improvements
Move the aggregation refresh into Fabric so it runs incrementally rather than on the full history.
Add a supplier reliability score, since lead-time variance drives more stockouts than lead-time length.
Field-level lineage documentation, so the inevitable "where does this number come from" question has an answer in the report itself.
Free resource
Power BI Performance Checklist
The 22 checks to run before declaring a semantic model finished.
Related course
From Power BI to Microsoft Fabric
Move from building reports to building the platform reports sit on: lakehouse architecture, medallion layering, Direct Lake and deployment pipelines.
Related builds and resources
Power BI Performance Checklist
The 22 checks to run before declaring a semantic model finished.
From Power BI to Microsoft Fabric
Move from building reports to building the platform reports sit on: lakehouse architecture, medallion layering, Direct Lake and deployment pipelines.
Power BI Dashboard Development
Dashboards built around the decisions they support, on a semantic model that stays fast as data grows.
Why does this measure ignore the slicer?
A user reports that this measure shows the same value no matter what they select in the Region slicer. Wait — that one is expected to be fixed to the…
Making Power BI Fast on 40 Million Rows
Aggregations, relationship configuration and the DAX rewrites that took a page from 8.4s to 0.6s.