Inventory Analytics with Power BI: Stock, Turnover, and Demand

Excess inventory costs 25-30% of its value annually in carrying costs. Stockouts cost retailers an estimated $1 trillion in lost sales each year. Between these two extremes lies the narrow band of optimal inventory — and Power BI is the tool that keeps operations teams precisely on that line.

The challenge with inventory analytics in Power BI is that stock is a snapshot measure (how much do we have right now?) rather than a flow measure (how much sold this month?). This distinction drives every design decision in the data model and every DAX calculation pattern. This guide covers the complete inventory analytics platform: data model, ABC classification, turnover analysis, reorder point calculations, and demand forecasting visualizations.

Key Takeaways

• Inventory is a point-in-time (snapshot) measure requiring different DAX patterns than sales metrics
• ABC analysis classifies items by revenue contribution: A (top 80%), B (next 15%), C (bottom 5%)
• Inventory turnover = COGS / Average Inventory — varies dramatically by industry
• Reorder Point = (Average Daily Usage × Lead Time) + Safety Stock
• DAX RANKX function powers ABC classification automatically as data changes
• Demand forecasting in Power BI uses linear regression via DAX or Azure ML integration
• Slow-moving and obsolete (SLOB) inventory identification saves significant carrying cost
• Power BI connects to ERP inventory tables (Odoo, SAP, NetSuite) without data movement

---

Data Model for Inventory Analytics

Core Inventory Tables

Inventory_Snapshot (one row per item per day/week — point-in-time stock levels):

Inventory_Movements (one row per stock transaction):

Sales_Lines (one row per sales order line for demand analysis):

• OrderID, ItemID, OrderDate, ShipDate, Quantity, UnitPrice, Revenue, CustomerID

Purchase_Orders (for lead time and procurement analysis):

• POID, ItemID, OrderDate, ExpectedDate, ReceiptDate, Quantity, UnitCost

Dim_Item (product dimension):

• ItemID, SKU, Name, Category, SubCategory, Supplier, LeadTimeDays, ReorderPoint, SafetyStock, UnitCost, ListPrice, IsActive

---

Core Inventory KPIs with DAX

Stock Level Measures

// Current Stock on Hand (point-in-time)
Stock on Hand =
CALCULATE(
    SUM(Inventory_Snapshot[QuantityOnHand]),
    Inventory_Snapshot[SnapshotDate] = MAX(Inventory_Snapshot[SnapshotDate])
)

// Stock Value (current)
Stock Value =
CALCULATE(
    SUM(Inventory_Snapshot[StockValue]),
    Inventory_Snapshot[SnapshotDate] = MAX(Inventory_Snapshot[SnapshotDate])
)

// Available to Promise (ATP)
Available to Promise =
CALCULATE(
    SUM(Inventory_Snapshot[QuantityAvailable]),
    Inventory_Snapshot[SnapshotDate] = MAX(Inventory_Snapshot[SnapshotDate])
)

// Stock on Order (incoming POs)
Stock on Order =
CALCULATE(
    SUM(Inventory_Snapshot[QuantityOnOrder]),
    Inventory_Snapshot[SnapshotDate] = MAX(Inventory_Snapshot[SnapshotDate])
)

// Projected Stock (current + on order - reserved)
Projected Stock = [Stock on Hand] + [Stock on Order] - [Stock Reserved]

Inventory Turnover

// COGS in Period (for turnover denominator)
Total COGS =
SUMX(
    FILTER(Inventory_Movements, Inventory_Movements[MovementType] = "Sale"),
    Inventory_Movements[Quantity] * Inventory_Movements[UnitCost]
)

// Average Inventory Value (beginning + ending / 2)
Avg Inventory Value =
AVERAGEX(
    VALUES(Date[Month]),
    CALCULATE(
        SUM(Inventory_Snapshot[StockValue]),
        Inventory_Snapshot[SnapshotDate] = MAX(Inventory_Snapshot[SnapshotDate])
    )
)

// Inventory Turnover Ratio
Inventory Turnover =
DIVIDE([Total COGS], [Avg Inventory Value], 0)

// Days Inventory Outstanding (DIO)
Days Inventory Outstanding =
DIVIDE(365, [Inventory Turnover], 0)

// Benchmark comparison (industry varies widely)
// Manufacturing: 6-12x | Retail: 4-6x | Electronics: 8-15x
Turnover vs Benchmark =
[Inventory Turnover] -
LOOKUPVALUE(
    Industry_Benchmark[InventoryTurnover],
    Industry_Benchmark[Category],
    SELECTEDVALUE(Dim_Item[Category])
)

---

ABC Analysis

ABC analysis classifies inventory items by their revenue or cost contribution:

• A items: Top 80% of revenue/COGS — high priority, tight control
• B items: Next 15% — moderate control
• C items: Bottom 5% — minimal oversight

// Revenue contribution per item (last 12 months)
Item Revenue 12M =
CALCULATE(
    SUM(Sales_Lines[Revenue]),
    DATESINPERIOD(Date[Date], TODAY(), -12, MONTH)
)

// Cumulative revenue % (for ABC cutoff)
Cumulative Revenue % =
DIVIDE(
    SUMX(
        FILTER(
            ALL(Dim_Item),
            RANKX(ALL(Dim_Item), [Item Revenue 12M], , DESC) <=
            RANKX(ALL(Dim_Item), [Item Revenue 12M], , DESC)
        ),
        [Item Revenue 12M]
    ),
    CALCULATE([Item Revenue 12M], ALL(Dim_Item)),
    0
)

// ABC Classification (calculated column in Dim_Item, refreshed periodically)
ABC Class =
VAR CumPct = [Cumulative Revenue %]
RETURN
SWITCH(TRUE(),
    CumPct <= 0.80, "A",
    CumPct <= 0.95, "B",
    "C"
)

// ABC Summary measure
A Items Count = CALCULATE(COUNTROWS(Dim_Item), Dim_Item[ABC Class] = "A")
A Items Revenue % = DIVIDE(
    CALCULATE([Item Revenue 12M], Dim_Item[ABC Class] = "A"),
    CALCULATE([Item Revenue 12M], ALL(Dim_Item)),
    0
)

ABC-XYZ Matrix

Extend ABC with XYZ classification for demand variability:

• X: Low demand variability (CV < 0.5) — predictable, plan for efficiency
• Y: Medium variability (CV 0.5-1.0) — some uncertainty
• Z: High variability (CV > 1.0) — unpredictable, plan for service level

// Coefficient of Variation for demand variability
Demand CV =
DIVIDE(
    STDEV.P(Sales_Lines[Quantity]),
    AVERAGE(Sales_Lines[Quantity]),
    0
)

// XYZ Classification
XYZ Class =
SWITCH(TRUE(),
    [Demand CV] < 0.5, "X",
    [Demand CV] < 1.0, "Y",
    "Z"
)

The AX segment (high revenue, predictable demand) receives the tightest reorder management. The CZ segment (low revenue, unpredictable) is a candidate for elimination or make-to-order.

---

Reorder Point and Safety Stock Calculations

Reorder Point Formula

Reorder Point = (Average Daily Usage × Lead Time) + Safety Stock

// Average Daily Usage (last 90 days)
Avg Daily Usage =
DIVIDE(
    CALCULATE(
        SUM(Sales_Lines[Quantity]),
        DATESINPERIOD(Date[Date], TODAY(), -90, DAY)
    ),
    90,
    0
)

// Reorder Point calculation
Calculated Reorder Point =
ROUND(
    [Avg Daily Usage] * AVERAGE(Dim_Item[LeadTimeDays]) +
    Dim_Item[SafetyStock],
    0
)

// Below Reorder Point flag
Below Reorder Point =
IF([Stock on Hand] < [Calculated Reorder Point], "Reorder Required", "OK")

// Days of Supply remaining
Days of Supply =
DIVIDE([Stock on Hand], [Avg Daily Usage], 0)

// Stockout Risk Score (0-100)
Stockout Risk =
SWITCH(TRUE(),
    [Days of Supply] < 7, 100,     -- Critical
    [Days of Supply] < 14, 75,     -- High risk
    [Days of Supply] < 30, 50,     -- Medium risk
    [Days of Supply] < 60, 25,     -- Low risk
    0                               -- OK
)

Safety Stock Calculation

// Safety Stock using statistical method
// SS = Z-score × σ(demand) × √Lead Time
Safety Stock Calculated =
VAR ZScore = 1.645   -- 95% service level
VAR DemandStdDev = STDEV.P(Sales_Lines[Quantity])  -- per day
VAR LeadTime = AVERAGE(Dim_Item[LeadTimeDays])
RETURN ROUND(ZScore * DemandStdDev * SQRT(LeadTime), 0)

---

Slow-Moving and Obsolete (SLOB) Inventory

Identifying SLOB inventory is critical for working capital optimization:

// Days Since Last Sale
Days Since Last Sale =
DATEDIFF(
    CALCULATE(
        MAX(Sales_Lines[OrderDate]),
        ALL(Date)
    ),
    TODAY(),
    DAY
)

// SLOB Classification
SLOB Class =
SWITCH(TRUE(),
    [Days Since Last Sale] > 365, "Obsolete",
    [Days Since Last Sale] > 180, "Slow Moving",
    [Days Since Last Sale] > 90, "At Risk",
    "Active"
)

// SLOB Inventory Value
SLOB Value =
CALCULATE(
    [Stock Value],
    Dim_Item[SLOB Class] IN {"Slow Moving", "Obsolete"}
)

// SLOB as % of total inventory
SLOB % =
DIVIDE([SLOB Value], [Stock Value], 0)

---

Demand Forecasting Visualization

Power BI can visualize demand forecasts using:

Built-in Forecast (Analytics Pane)

Right-click a line chart → Analytics pane → Forecast:

• Forecast length: 12 months
• Confidence interval: 95%
• Seasonality: Auto-detect

This uses Exponential Smoothing (ETS) algorithm — suitable for simple, stationary demand patterns.

Custom DAX Linear Forecast

// Simple Linear Regression Forecast
Demand Forecast =
VAR LastPeriod = MAX(Date[MonthNum])
VAR ForecastPeriod = LastPeriod + 1  -- Next month
VAR N = COUNTROWS(VALUES(Date[Month]))
VAR SumX = SUMX(VALUES(Date[MonthNum]), Date[MonthNum])
VAR SumY = SUMX(VALUES(Date[Month]), [Monthly Sales Qty])
VAR SumXY = SUMX(VALUES(Date[Month]), Date[MonthNum] * [Monthly Sales Qty])
VAR SumX2 = SUMX(VALUES(Date[MonthNum]), Date[MonthNum]^2)
VAR Slope = DIVIDE(N*SumXY - SumX*SumY, N*SumX2 - SumX^2, 0)
VAR Intercept = DIVIDE(SumY - Slope*SumX, N, 0)
RETURN Intercept + Slope * ForecastPeriod

Azure ML Demand Forecasting

For sophisticated demand forecasting, integrate Azure Machine Learning:

1. Train a Prophet or ARIMA model on historical demand data in Azure ML
2. Deploy as an Azure ML web service
3. Call from Power BI dataflows using the AI Insights integration
4. Surface forecast values as a column in the item dimension

---

Inventory Dashboard Architecture

Page 1: Executive Inventory Summary

• Total Stock Value (KPI card with MoM change)
• Inventory Turnover (gauge vs industry benchmark)
• Days Inventory Outstanding (KPI with trend)
• Stockout Items Count (alert card, red if >0)
• SLOB Value (KPI with % of total)
• Stock Value by Category (treemap)
• Reorder Alerts (table: item, QoH, reorder point, days of supply)

Page 2: ABC Analysis

• Pareto chart (items ranked by revenue, cumulative %)
• ABC distribution (donut chart: count and value by class)
• ABC-XYZ matrix (scatter plot: revenue on X, CV on Y, bubble size = stock value)
• Top A items table (item, revenue, turnover, stock value, margin)

Page 3: Stock Monitoring

• Stock level heatmap (location × category)
• Below reorder point items (table with stockout risk color)
• Incoming PO timeline (Gantt or bar chart)
• Stock age analysis (bar chart: 0-30, 30-60, 60-90, 90+ days)

Page 4: Demand and Forecast

• Actual demand vs forecast (line chart with forecast shaded)
• Demand variability by category (box plot or bar with error bars)
• Seasonal demand patterns (heat map: months × day of week)
• Top 20 fast movers (bar chart by weekly units sold)

---

Frequently Asked Questions

What is the best way to connect Power BI to an ERP for inventory data?

The connection method depends on your ERP. For Odoo, connect directly to PostgreSQL on a read replica. For SAP, use the SAP HANA connector with inventory CDS views. For NetSuite, use SuiteAnalytics Connect ODBC. For Dynamics 365 Business Central, use the Business Central connector. For all ERP connections, use a dedicated analytics user account with read-only access to inventory tables, and schedule refreshes off-peak hours to minimize ERP load.

How do I handle multi-warehouse inventory in Power BI?

Add a Location dimension to your data model with attributes like warehouse name, city, country, and type (distribution center, retail store, etc.). All inventory snapshot rows include a LocationID. Build measures that either aggregate across all locations or filter by selected location via slicer. For inter-warehouse transfer analysis, the Inventory_Movements table with MovementType = "Transfer" tracks stock moving between locations.

What is a good inventory turnover ratio?

It depends heavily on industry. Electronics: 8-15x (high velocity, low margins). Grocery/FMCG: 15-30x. Automotive parts: 3-6x. Fashion retail: 4-8x (seasonal). Industrial manufacturing: 3-8x. Compare your turnover ratio against your industry benchmark rather than a generic target. An "ideal" ratio balances service level (avoid stockouts) against carrying cost (avoid excess).

Can Power BI predict when I will run out of stock?

Yes — the "Days of Supply" measure calculates how many days current stock covers at average daily sales rate. When this falls below your lead time + safety stock buffer, Power BI can flag the item as at risk and show it in a reorder alert table. For predictive stocking, integrate Azure ML demand forecasting to project future sales and calculate when stockout risk becomes critical based on forecasted rather than historical demand.

How should I visualize inventory aging in Power BI?

Use a stacked bar chart showing the percentage of stock value in each age bucket (0-30 days, 31-60, 61-90, 91-180, 180+ days). Aging is calculated from the receipt date of the oldest batch. Track this trend over time to see if your aging profile is improving (moving toward fresher stock) or deteriorating (accumulating older inventory). Highlight 90+ day stock in red as a SLOB risk indicator.

---

Next Steps

Effective inventory analytics in Power BI reduces carrying costs, prevents stockouts, and improves cash flow — the three metrics that matter most to supply chain and operations leadership. Getting the data model right (snapshot-based stock levels, movement-based flow analysis) is the foundation everything else builds on.

ECOSIRE's Power BI team builds supply chain and inventory dashboards connected to your ERP systems — Odoo, SAP, NetSuite, Dynamics 365, and others. We implement ABC analysis, reorder alert systems, and demand forecasting visualization as production-ready dashboards.

Explore our Power BI dashboard development services for supply chain analytics implementation, or contact our team to discuss your inventory data sources and analytics requirements.