Calculate a Running Daily Inventory

Hi,

I have solved a similar question in the attached files.

Hope this helps.

Hi jfcarter66,

Step 1: Create sample tables (Enter Data)
In Power BI Desktop, go to Home and select Enter data to create the following three tables.
Inventory
Item   Date   EndingInventory
A   2025-09-01   100
B   2025-09-01   50
Receipts_Wide (wide format, source of the original issue)
Item   2025-09-02   2025-09-03   2025-09-04
A   10   5   0
B   0   20   10
Usage_Wide (wide format)
Item   2025-09-02   2025-09-03   2025-09-04
A   8   12   3
B   1   2   4

Step 2: Unpivot the wide tables (Power Query)

1. In Power Query, select the query (e.g., Receipts_Wide).
2. Select the Item column, then click Unpivot Other Columns on the Transform ribbon.
3. Rename columns: Attribute → Date, Value → Quantity.
4. Change the Date column type to date and Quantity to whole number.
5. Rename the query to Receipts (do the same for Usage → Usage).

Step 3:  Create an Items table
Items = DISTINCT(Inventory[Item])

1. Relate Items[Item] to Inventory[Item] (one-to-many).
2. Relate Items[Item] to Receipts[Item] (one-to-many).
3. Relate Items[Item] to Usage[Item] (one-to-many).

Step 4: Create Calendar Table

Dates = CALENDAR ( DATE(2025,9,1), DATE(2025,9,4) )
Calendar[Date] → Usage[Date] (many-to-one, single direction).
Calendar[Date] → Rates[Date] (many-to-one, single direction).

Step 5: Create this Measure

Running Inventory =
VAR _Item = SELECTEDVALUE( Inventory[Item] )
VAR _Date = MAX( Dates[Date] )

VAR _Base =
CALCULATE(
MAX( Inventory[EndingInventory] ),
FILTER( ALL( Dates ), Dates[Date] <= _Date ),
Inventory[Item] = _Item
)

VAR _Receipts =
CALCULATE(
SUM( Receipts[Quantity] ),
FILTER( ALL( Receipts ), Receipts[Item] = _Item && Receipts[Date] <= _Date )
)

VAR _Usage =
CALCULATE(
SUM( Usage[Quantity] ),
FILTER( ALL( Usage ), Usage[Item] = _Item && Usage[Date] <= _Date )
)

RETURN
IF(
ISBLANK(_Base) && ISBLANK(_Receipts) && ISBLANK(_Usage),
BLANK(),
COALESCE(_Base,0) + COALESCE(_Receipts,0) - COALESCE(_Usage,0)
)

Step 6: Test in a Table
Create a table visual with:
Dates[Date]
Inventory[Item]
The Running Inventory measure

Additionally, I have included the PBIX file that I created using the provided sample data. Kindly review it and confirm whether it aligns with your expectations.

Thank you.