Projecting inventory with current week as starting point

[BGARRECHT]

Hi,

I'm trying to create a measure that uses the current week as the starting point for inventory.  

From there, I want to add forecasted production, and open purchase orders arriving in the week.  

I want to then subtract forecasted sales volume.  

Starting Inventory + Production Orders + Purchase Orders - Sales Forecast

Then I want this as the starting point for next week, so I can then add production orders/purchase orders and subtract next weeks sale forecast. 

The goal is to use this to forecast volume. 

Anyone have experience in this and can point me in the right direction?

[AshokKunwar · Accepted solution]

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 

[BGARRECHT]

Got this to work tonight.  I did have to change the formula a little.  

If you want to put this in the original post that I marked as the solution. 

Thank you again for taking the time to help me!

Below is what works:

ROLLING_INVENTORY =

VAR _MaxDate = MAX(Dates[Date])

VAR _StartingStock = CALCULATE([INV OnHand QTY2],FILTER(ALL('Dates'),Dates[Date] <= [_MaxDate]))

VAR _CumulativeAdditions = CALCULATE([PRODUCTIONQTY] + [Open_PO_QTY],FILTER(ALLSELECTED(Dates[Date]),Dates[Date] <= _MaxDate))

VAR _CumulativeSubtractions = CALCULATE(sum(TOTAL_SALES_FORECAST[Volume Projection]) + [COMPQTY],FILTER(ALLSELECTED(Dates[Date]),Dates[Date] <= _MaxDate))

RETURN

_StartingStock + _CumulativeAdditions - _CumulativeSubtractions

[BGARRECHT]

Will the   FILTER(ALL('Date'), 'Date'[Date] <= _MaxDate) work for projecting future inventory though?  Here is an example of what' I'm trying to do:  

xample:

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.

[AshokKunwar]

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 CumulativeSubtractions

Why 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

[v-priyankata]

Hi @BGARRECHT 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@rubayatyasmin @danextian Thanks for the inputs.

I hope the information provided by users was helpful. please look into old threads as shared by user, If you still have questions, as asked by user please provide the sample data, so we can work on the things.

[danextian]

Hi @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.

[BGARRECHT]

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.

[AshokKunwar]

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 CumulativeSubtractions

 Why 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 

[BGARRECHT]

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.  

  

[AshokKunwar]

Hii @BGARRECHT 

ROLLING_INVENTORY = 
VAR _MaxDate = MAX(Dates[Date])

// 1. Get your initial starting stock (Static baseline)
VAR _StartingStock = CALCULATE([INV OnHand QTY2], ALL('Dates'))

// 2. Calculate Cumulative Additions (Summing separately to avoid multiplication errors)
VAR _cumulativeAdditions = 
    CALCULATE(
        SUM('YourTable'[HEADERQTY]) + SUM('YourTable'[Open_PO_QTY]),
        FILTER(ALL('Dates'), Dates[Date] <= _MaxDate)
    )

// 3. Calculate Cumulative Subtractions (Comp Qty + Sales/Volume Projection)
VAR _cumulativeSubtractions = 
    CALCULATE(
        SUM('YourTable'[COMPQTY]) + [Sum of Volume Projection],
        FILTER(ALL('Dates'), Dates[Date] <= _MaxDate)
    )

RETURN
_StartingStock + _cumulativeAdditions - _cumulativeSubtractions

• ​Avoiding the Multiplier: Your original DAX used [HEADERQTY] * [Open_PO_QTY]. If those are measures, multiplying them inside a CALCULATE on a filtered table often leads to massive, unexpected numbers. Summing them individually before adding them together keeps the math grounded.
• ​The Running Balance: By using Dates[Date] <= _MaxDate, the formula calculates everything that happened from the beginning of time up to the current week in your row.
• ​The Flow: For week 26-02, it takes your 34.6M, adds the 129k PO, and subtracts your projections. Then, for week 26-03, it automatically includes 26-02's data in the "cumulative" sum, effectively making the previous week's ending balance your new starting point.

​Quick Formatting Tip:

​If the numbers still look slightly off, double-check that your [INV OnHand QTY2] measure isn't already changing per week. If it is a "Snapshot" of today's inventory, the ALL('Dates') part is perfect. If it's a moving value, we might need to wrap it in a FIRSTDATE or LASTDATE filter.

​Give this a try, and you should see week 26-03 starting exactly where 26-02 ended!

don't forget to mark my post as a solution!

[BGARRECHT]

I'm not sure what you mean by avoid the multiplier?  

I broke it out further so we can see what each VAR is calculating.  

_StartingStock is 100% accurate, 35,685,742

However, _CumulativeAddition is 115,292,261, when it should show that week as 129,800. 

_CumulativeSubtraction shows 250,397 instead of 0 (There is no volume projection week 2)

So we should see in week 26-2.  

Starting Inventory: 35,685,742

+ Cumulative Addition: 129,800

- Cumulative Subtraction: 0

Ending inventory for the week : 34,815,542

Week 26-03 _startingstock should then be 34,815,542

It looks like cumulative addition and cumulative subtraction is adding values from before the date range slicer, because they should be equal to that weeks data.  

Attached is example with everything broken out and DAX below.  

ROLLING_INVENTORY =

VAR _MaxDate = MAX(Dates[Date])

VAR _StartingStock = CALCULATE([INV OnHand QTY2],ALL('Dates'))

VAR _CumulativeAdditions = CALCULATE([HEADERQTY] + [Open_PO_QTY],FILTER(ALL('Dates'),Dates[Date] <= _MaxDate))

VAR _CumulativeSubtractions = CALCULATE(sum(TOTAL_SALES_FORECAST[Volume Projection]) + [COMPQTY],FILTER(ALL('Dates'),Dates[Date] <= _MaxDate))

RETURN

_StartingStock + _CumulativeAdditions - _CumulativeSubtractions

 

 

[rubayatyasmin]

Hi @BGARRECHT 

Yes, this is rolling inventory issue in PBI. 

This post might be helpful, 

Mastering Dynamic Stock Management with Cumulative... - Microsoft Fabric Community

Solved: rolling inventory over time - Microsoft Fabric Community

If these are not helpful, then I would suggest adding some dummy data. 

[BGARRECHT]

I'll try this too and get back to you, I truly appreciate everyones help with this