The company is an importer and wholesale distributor of cosmetics. Sales channels: salons, distributors, retail, marketplaces (Shopify, Amazon, Etsy, Walmart), consignment. The portfolio is ~700 active SKUs across three warehouses and several accounts. The problem wasn't "is there stock or not" but how to count inventory for a wholesaler:

• Stockout gaps. By the time the team noticed an item was missing, sales were already lost. Recovering through a rush order means a few extra weeks because of overseas logistics.
• Rigid reporting. An ERP report is built once and for all. A new question from management — reworking the form is slow and expensive.
• "Out of stock" isn't about zero. For a wholesaler an item is "gone" when it drops below a comfortable threshold (e.g. below the minimum shipment). A different threshold for different SKUs.
• Not every replenishment starts a new period. If a partner returned goods — that's an accounting event, not a resumption of sales. Counting daily averages from that "replenishment" corrupts the calculation.
• ERP customization — slow and expensive. Every refinement of business logic through a developer is weeks and an invoice.

Architecture

The key decision — pull only raw data out of the ERP (stock, sales, POs) and compute all the business logic in the Excel model via DAX. That buys two things: rules can change without bothering an ERP developer; report customization takes minutes, not weeks.

The frame approach to stock

The core concept is the availability frame. It's the period during which an item was actually selling (not sitting in returns or below threshold). Daily sales are computed only inside frames.

Frame parameters (specific to each SKU and warehouse) live in Google Sheets:

Daily sales: rolling windows

The base measure is sales over the last 30 days, with an automatic "right edge": either the current calendar context, or the last actual sales date (relevant if the file is opened on a different day):

-- Units Sold (last 30 days)
VAR BaseDate =
    IF(
        ISFILTERED('Calendar'[Date]) || ISCROSSFILTERED('Calendar'),
        MAX('Calendar'[Date]),
        MAX('Sales'[Date])
    )
RETURN
    CALCULATE(
        [Units Sold],
        DATESINPERIOD('Calendar'[Date], BaseDate, -30, DAY)
    )

In parallel a trend measure is computed — the ratio of the last 30 days to the average monthly intensity 31–90 days ago. That instantly shows whether an item is accelerating, slowing or stable.

-- Sales dynamics: (30d) / (avg month over the 31-90d window)
IF(
    [Units Sold (last 30 days)] &&
    [Units Sold (avg month over 30-90 window)],
    [Units Sold (last 30 days)] /
    [Units Sold (avg month over 30-90 window)] - 1
)

The buyer sees not just "how many days of stock are left" but "where the item is heading". That changes priorities: an accelerating item with a small buffer — order first; a slowing one with a big buffer — can wait.

Stock at the "right" date, per entity

The subtlety that breaks naive measures: daily stock is stored as snapshots on specific dates, but not every SKU has a record for every day. You need the latest known date per SKU, separately for the central warehouse:

-- Absolute stock, CW (central warehouse)
VAR LastDate1 =
    CALCULATE(
        MAX('Stock'[Date]),
        ALLEXCEPT('Stock', 'Stock'[Item.SKU]),
        'Stock'[Entity] = "[Central WH]"
    )
VAR Result =
    CALCULATE(
        SUM('Stock'[Qty]),
        'Stock'[Date] = LastDate1,
        'Stock'[Entity] = "[Central WH]"
    )
RETURN Result

For the aggregate picture (all warehouses) — a different pattern: SUMX over SUMMARIZE by entity and SKU, with LASTDATE inside. Each "entity × SKU" combination gets its own latest record:

-- End-of-period stock (aggregate across warehouses)
SUMX(
    SUMMARIZE('Stock', 'Stock'[Entity], 'Stock'[Item.SKU]),
    CALCULATE(
        SUM('Stock'[Qty]),
        LASTDATE('Stock'[Date]),
        'Stock'[Qty] <> BLANK()
    )
)

Days of cover and the recommendation

With stock and daily sales in hand, we compute a simple but informative metric:

-- Months of cover
DIVIDE(
    [End-of-period stock],
    [Units Sold (last 30 days)],
    "infinity"
)

The order-quantity recommendation is assembled from three terms:

If the result is negative — no order needed. If positive — it's a "raw" recommendation the manager cross-checks against the ABC class and the trend measure.

ABC analysis: two-dimensional

The model holds two versions of the ABC classification, precomputed (in the ETL stage) and placed in the ABC ALL table:

• ALL — classification across the whole portfolio. Useful for overall ranking and purchasing priority.
• InBrand — classification within a brand. Useful for assessing a specific brand: even a B-rank item can be a "workhorse" inside a narrow line.

-- In-brand grade (via a measure rather than a direct column —
-- so it reacts to empty sales in context)
IF([Units Sold],
    SUM('ABC ALL'[Percentage InBrand])
)

The buyer looks first at the intersections: A by ALL and A by InBrand with zero stock — an item where the loss is immediately visible.

Inputs from Google Sheets

One Google Sheet — two cases. The same sheet that holds the frame parameters (lead time, thresholds, target stock) also holds the pricing parameters (RRP, wholesale, price types). Power Query pulls it as an ordinary table:

-- Prices from GS (simplified)
let
    Source = Csv.Document(
        Web.Contents("https://docs.google.com/spreadsheets/d/.../export?format=csv&gid=..."),
        [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
    Header = Table.PromoteHeaders(Source),
    Types  = Table.TransformColumnTypes(Header, {
        {"Item.SKU",            type text},
        {"Lead time, days",     Int64.Type},
        {"Zero threshold CW",   Int64.Type},
        {"Flag threshold",      Int64.Type},
        {"Target stock, days",  Int64.Type}
    })
in
    Types

In DAX measures the parameters are pulled in via LOOKUPVALUE or relationships to the fact tables by SKU. A value changes in GS — on the next "Refresh all" it lands in the model and recalculates everything that depends on it.