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