Forum Discussion
Projecting inventory with current week as starting point
Hi BGARRECHT
To achieve a rolling inventory forecast where each week's starting point depends on the previous week's closing balance, you need to use a Cumulative (Running Total) DAX pattern.
Instead of trying to "pass" a value from row to row, the most stable way to do this in Power BI is to calculate the sum of all transactions from the beginning of your data up to the specific week on each row.
The Solution: Inventory Projection Measure
Use the following measure. It combines your current On-Hand inventory with the running total of your forecasted "In" (Production/PO) and "Out" (Sales) movements.
Projected_Inventory =
VAR _MaxDate = MAX('Date'[Date]) -- Assumes a relationship with a Date table
-- 1. Get current physical stock (The Baseline)
VAR _StartingStock = [INV OnHand QTY2]
-- 2. Calculate running total of additions (Production + POs)
VAR _RunningAdditions =
CALCULATE(
[HEADERQTY] + [Open_PO_QTY],
FILTER(ALL('Date'), 'Date'[Date] <= _MaxDate)
)
-- 3. Calculate running total of subtractions (Sales Forecast)
VAR _RunningSubtractions =
CALCULATE(
[Sum of Volume Projection],
FILTER(ALL('Date'), 'Date'[Date] <= _MaxDate)
)
-- 4. Final Logic: Baseline + All In - All Out
RETURN
_StartingStock + _RunningAdditions
- _RunningSubtractions
Why this works:
FILTER(ALL('Date'), ... <= _MaxDate): This is the "magic" part. It tells Power BI to look at the entire timeline up to the current row, allowing the measure to accumulate values week-over-week.
Stability: Because it calculates the total history for every row, your forecast won't break if you change the sort order or skip a week in your visual.
Starting Point: By including [INV OnHand QTY2], you ensure the forecast always anchors to your real-world current inventory level.
Implementation Checklist:
Ensure you have a Date/Calendar table marked as a date table in your model.
If your "Starting Stock" is only a single value (not a time-series), ensure your [INV OnHand QTY2] measure is wrapped in a CALCULATE(..., ALL('Date')) so it remains constant across all future weeks.
Visual Tip: In your Matrix, go to Format > Cell elements and turn on Icons. Set a "Red Diamond" icon for whenever [Projected_Inventory] < 0 to highlight potential stockouts immediately.
If this helps you project your volume correctly, please mark this as an "Accepted Solution" so it helps others in the community!
Best regards,
Vishwanath
Super UserHi BGARRECHT
Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy (confidential data removed) of your PBIX stored in the cloud.
How can I attach a excel file here, it says " The file type .xlsx is not accepted"
Example:
Inventory level is starting inventory as of 1/4/26.
ADD - production orders expected to complete between 1/4-/10
ADD - purchase orders expected to arrive between 1/4 - 1/10
SUBTRACT- expected sales between 1/4/-1/10
Ending inventory is projected to end on 1/10
Use this # then as starting inventory level for week starting 1/11, and continue step to project inventory for future weeks.
AshokKunwarContinued Contributor
Hii BGARRECHT
The most stable and performant way to do this is using the FILTER(ALL('Date'), ...) pattern. This approach is "set-based," meaning it calculates the net change of all history up to the current row's date
The Master Inventory Measure
Use this measure to calculate your projected ending inventory for any future week:
$$Projected \ Inventory =$$
VAR \ \textunderscore MaxDate = MAX('Date'[Date]) \ \text{// The date on the current row of your visual}VAR \ \textunderscore StartingStock = CALCULATE([Total OnHand], ALL('Date')) \ \text{// Your actual stock today}$$VAR \ \textunderscore CumulativeAdditions = $$
CALCULATE( [Production] + [PurchaseOrders], FILTER(ALL('Date'), 'Date'[Date] <= \textunderscore MaxDate) )$$VAR \ \textunderscore CumulativeSubtractions = $$
CALCULATE( [ProjectedSales], FILTER(ALL('Date'), 'Date'[Date] <= \textunderscore MaxDate) )RETURN
\textunderscore StartingStock + \textunderscore CumulativeAdditions - \textunderscore CumulativeSubtractionsWhy this is a Solution:
Eliminates Recursion: Traditional "Previous Week + In - Out" logic is recursive and slow. This "Set-Based" logic allows the Storage Engine to sum all values in one pass, potentially reducing that 230s lag to under 1s.
Stability: Because it uses ALL('Date'), the calculation won't break if you filter your visual to only show "Next Month"—it still "knows" what happened in the past to reach that starting point.
Baseline Anchor: Wrapping your [Total OnHand] in ALL('Date') ensures your "Current Reality" is the starting point for every future calculation, regardless of the date filter on your report.
Implementation Checklist:
Mark as Date Table: Ensure your Calendar table is officially "Marked as Date Table" in the Model View.
Check Relationships: All three sources (Production, POs, and Sales) must have active relationships with your Date[Date] column.
Performance: If your model is massive, use Variables (VAR) as shown above. This prevents the engine from recalculating the _MaxDate multiple times within the same measure.
If this cumulative logic fixed your inventory forecast and cleared your resource errors, please mark this as an "Accepted Solution" to help the community!
Best regards,
Vishwanath
It looks like the starting point calculation works, but now cumulative addition is adding too much. This is a greating starting point though. See my attachments below.
For week 26-02, I want the starting inventory to be 34,685,742, which matches your starting inventory formula. However when I add cumulativeadditions, it bumps the number up to 149,968.003. When it should only add the open PO qty of 129,800.
I want the headerqty and po qty for that week to add to the starting point. Then the compqty and volume projection to decrease the inventory.
The 34,815,542 should be the starting point for week 26-03.
