Calculate a Running Daily Inventory

[jfcarter66]

I am working on a dashboard and need some help.  I have measures that calculate current inventory.  I want to display subsequent daily projected inventory based on Current Ending inventory adding projected incoming items and subtracting projected useage.  

The running inventory is what I want my visual to display.  

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar question in the attached files.

Hope this helps.

[v-sgandrathi · Accepted solution]

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.

[v-sgandrathi]

Hi @jfcarter66,

As we have not received a response from you yet, I would like to confirm whether you have successfully resolved the issue or if you require further assistance.

Thank you for your cooperation. Have a great day.

[v-sgandrathi]

Hi @jfcarter66,

Just wanted to follow up and confirm that everything has been going well on this. Please let me know if there’s anything from our end.
Please feel free to reach out Microsoft fabric community forum.

[v-sgandrathi]

Hi @jfcarter66,

I wanted to check if you had the opportunity to review the information provided by @Ashish_Mathur, @ryan_mayu  and @Greg_Deckler. Please feel free to contact us if you have any further questions. 
 

Thank you and continue using Microsoft Fabric Community Forum.

[jfcarter66]

I will be looking at the replys today.  thank you.

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar question in the attached files.

Hope this helps.

[ryan_mayu]

@jfcarter66 

pls see the attachment below

[jfcarter66]

I attempted to do this calculation in my dashboard.  I am getting error "The SUM function only accepts a column reference as an argument.

Thoughts?

[Greg_Deckler]

@jfcarter66 I would first unpivot your Projected Receipts and Useage Tables and you probably will want a central Date table as well.

Measure =
  VAR __Item = MAX( 'Inventory'[Item] ) // whatever you are using in your matrix visual
  VAR __Date = MAX( 'Dates'[Date] ) // whatever you are using for your columns
  VAR __Base = MAX( 'Inventory'[WEIGHT]
  VAR __Receipts = SUMX( FILTER( ALL( 'Projected Receipts' ), [Item] = __Item && [Date] <= __Date ), [Value] )
  VAR __Useage = SUMX( FILTER( ALL( 'Useage' ), [Item] = __Item && [Date] <= __Date ), [Value] )
  VAR __Result = __Base + __Receipts - __Useage
RETURN
  __Result