Store Stockout Bulletin

The problem

A fashion retail chain tracked store stockouts in spreadsheets. The overall number existed, but it did not answer the question that matters for replenishment: of these gaps, which ones can I fix this week?

If the product ran out in the store and in the distribution center too, there is no point pushing the replenishment team. If it ran out in the store while stock sits in the distribution center, that is a replenishment failure that can be fixed now.

The solution

A weekly bulletin in Power BI, fed by a dataset prepared with SQL and VBA, built on three ideas:

1. Total stockout: the share of store-SKU combinations at zero on the day of the stock snapshot.
2. Actionable stockout: the same calculation, only for SKUs that have stock in the distribution center. This is the part replenishment can solve.
3. Store ranking: lagging and leading stores of the week, so coordinators know where to act first.

The filters follow the way the business looks at the operation: channel (company-owned or franchise), product line (new in, core, basics and seasonal), region and manager. Products being phased out are left out, so items that will not be replenished do not inflate the stockout.

The main DAX measures

-- % of store-SKUs at zero, only among items the store has already received
% Ruptura 1D =
AVERAGEX(
    FILTER( Ruptura, Ruptura[ESTOQUE_RECEBIDO] = "RECEBIDO" ),
    Ruptura[RUPTURA_1D]
)

-- Same calculation, only where the distribution center has stock to replenish
% Ruptura Tratável =
CALCULATE( [% Ruptura 1D], Ruptura[ESTOQUE_TRATAVEL] = "TRATAVEL" )

-- Lost potential sales: days out of stock x the SKU's sales potential in the store
Valor Ruptura 7D =
SUMX( Ruptura, Ruptura[RUPTURA_7D] * Ruptura[POTENCIAL] )

Measure and column names are in Portuguese, as in the original model.

The result

The bulletin went out every week to store managers and coordinators, with the same criteria for every store, and sped up replenishment priorities for company-owned stores. The goal is to replace "stockout is high" with "these stores have these SKUs at zero while the distribution center has stock".