Forum Discussion
Projecting inventory with current week as starting point
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?
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
13 Replies
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.I'll try this too and get back to you, I truly appreciate everyones help with this
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.
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
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.
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
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.
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
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
