Retaining historical data

1) Create an append-only snapshot in SQL Server (recommended)

You currently have a table/view with current stock only (it keeps changing). We’ll add a new table that appends one row per item/warehouse per snapshot date. That becomes your history.

a) Make the table

-- Run once
CREATE TABLE dbo.InventorySnapshot (
SnapshotDate date NOT NULL,
ItemID int NOT NULL,
WarehouseID int NOT NULL,
QtyOnHand decimal(18,2) NOT NULL,
CONSTRAINT PK_InventorySnapshot PRIMARY KEY (SnapshotDate, ItemID, WarehouseID)
);

b) Make a stored procedure that captures “today”

CREATE OR ALTER PROCEDURE dbo.CaptureInventorySnapshot
AS
BEGIN
SET NOCOUNT ON;

DECLARE @d date = CAST(GETDATE() AS date);

INSERT INTO dbo.InventorySnapshot (SnapshotDate, ItemID, WarehouseID, QtyOnHand)
SELECT
@d,
ci.ItemID,
ci.WarehouseID,
ci.QtyOnHand
FROM dbo.CurrentInventory AS ci -- <-- replace with your source table or view
WHERE NOT EXISTS (
SELECT 1
FROM dbo.InventorySnapshot s
WHERE s.SnapshotDate = @d
AND s.ItemID = ci.ItemID
AND s.WarehouseID = ci.WarehouseID
);
END

c) Schedule it

* SQL Server Agent → create a job to EXEC dbo.CaptureInventorySnapshot on your chosen cadence:

• Weekly (to mirror your refresh) and
• An extra run on the last day of each month (e.g., 23:55) so you always have a month-end point.

* No Agent? Use Windows Task Scheduler + sqlcmd to run the proc on a schedule.

Result: your snapshot table grows over time (append-only). You now have history.

2) Model the data in Power BI

a) Bring in tables

• InventorySnapshot (Import mode).
• A proper Date table (covering the period you’ll analyze). Mark it as Date table.

b) Relationships

Relate Date[Date] → InventorySnapshot[SnapshotDate] (many-to-one, single, active).

3) Measures for month-by-month line chart

You want each month to show the stock level as of the last snapshot in that month (or the latest snapshot on/before the month end).

You like MAXX(ALLSELECTED(...)), so I’ll lean into that pattern.

SOH (as of last selected date) :=
VAR SelMaxDate =
MAXX(ALLSELECTED('Date'[Date]), 'Date'[Date])
VAR LastSnapOnOrBeforeSel =
CALCULATE(
MAX(InventorySnapshot[SnapshotDate]),
ALL(InventorySnapshot[SnapshotDate]),
InventorySnapshot[SnapshotDate] <= SelMaxDate
)
RETURN
CALCULATE(
SUM(InventorySnapshot[QtyOnHand]),
InventorySnapshot[SnapshotDate] = LastSnapOnOrBeforeSel
)

For a monthly line (clean month-end points):

SOH (Month End) :=
VAR EOM = EOMONTH( MAX('Date'[Date]), 0 )
VAR StartOfMonth = DATE( YEAR(EOM), MONTH(EOM), 1 )
VAR LastSnapInMonth =
CALCULATE(
MAX(InventorySnapshot[SnapshotDate]),
FILTER(
ALL(InventorySnapshot[SnapshotDate]),
InventorySnapshot[SnapshotDate] >= StartOfMonth &&
InventorySnapshot[SnapshotDate] <= EOM
)
)
RETURN
IF(
NOT ISBLANK(LastSnapInMonth),
CALCULATE(
SUM(InventorySnapshot[QtyOnHand]),
InventorySnapshot[SnapshotDate] = LastSnapInMonth
)
)

Visual setup

• Axis: Date[Month] (e.g., a Month Year column or the Date hierarchy at Month level).
• Values: SOH (Month End).
• Legend (optional): Item, Warehouse, or Category.

This yields a smooth month-by-month line from the snapshots you’re capturing.

4) Add “under-stock” detection (optional KPI)

If you keep a target/min stock per item (e.g., Item[MinStock]):

Understock (Month End) :=
VAR Val = [SOH (Month End)]
VAR MinStock = SELECTEDVALUE( Item[MinStock], 0 )
RETURN IF( NOT ISBLANK(Val) && Val < MinStock, 1, 0 )

5) Performance & housekeeping

• Add an index on (ItemID, WarehouseID, SnapshotDate) if the table grows large.
• In Power BI, enable Incremental Refresh for InventorySnapshot (e.g., keep last 36 months, refresh last 2 months).
• If a month has no snapshot, the measure automatically picks the last snapshot before the selected date (from SOH (as of last selected date)), so your chart remains continuous.

6) Can’t touch SQL Server? Two solid alternatives

A) Power Automate (no manual work)

• Schedule a flow: SQL Server (Get rows) → append to Excel table in OneDrive/SharePoint or to another SQL table (Azure SQL, etc.).
• The flow runs weekly + month-end, creating the same append-only history.
• Point Power BI to the archive (Excel/SQL) and reuse the DAX above.

B) If you have a transactions table

* If there’s a fact like InventoryMovements (Receipts/Issues with dates), you can compute stock position by cumulative sum up to month end:

• Stock as of date = Opening + SUM(Receipts – Issues up to that date).

* This avoids snapshots entirely. But if you only have a “current stock” table, snapshots are the right route.

That’s it—you’ll preserve history automatically and get a clear month-to-month line of stock levels to spot where you were under-stocked.

I hope it will help.