# Forecasting Opening and Closing Inventory

#### gkaropo

##### New Member
[FONT=&quot]Hello all,
new to the forum.
I have an issue with powerpivot/DAX that I have not been able to solve. I hope someone is up for the challenge...

The problem is calculating a YTD monthly Inventory closing position while ignoring cumulative negatives if there is no stock to satisfy the forecast.
Kind of hard to explain but the attached file shows my desired outcome.

[/FONT]

[FONT=&quot][/FONT][FONT=&quot]Im using the following
InvMovement:=[PURCHASES]-[SALES]
OPENING INV:=calculate([InvMovement],FILTER(ALL(‘DATE'[DATE]),’DATE'[DATE]< MAX(‘DATE'[DATE])))+[CURRINV]
ENDING INV:=calculate([InvMovement],FILTER(ALL(‘DATE'[DATE]),’DATE'[DATE]<= MAX(‘DATE'[DATE])))+[CURRINV][/FONT]

[FONT=&quot][/FONT][FONT=&quot]the problem is that there cases where my Sales Team is forecasting above the available inventory in a future period.
When we are out of Stock we still want the forecast to remain, even though we cant fill the sale.
The above OPENING INV and ENDING INV calculations work when inventory is always positive, but accumulates the negatives on Out of Stocks and results in incorrect (negative) opening inventory positions.[/FONT]

[FONT=&quot][/FONT][FONT=&quot]Id like the ENDING INV results to be 0 if it calculates as negative and OPENING INV of the following period to also be zero.

https://1drv.ms/x/s!AgsUyh0i7varnjMfLmnZ1bPgSSGp[/FONT]

[FONT=&quot][/FONT][FONT=&quot] THANKS!!![/FONT]

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### Ozeroth

##### Active Member
Hello gkaropo

Welcome to the forum

The logic I have used here (and in similar situations in the past)
1. Based on the original inflows/outflows, find the most negative inventory balance that has occurred to date.
2. Add this back to the originally calculated inventory balance

I created some additional measures to handle this and modified your existing measures. The resulting measures are:
Code:
``````[B]ENDING INV UNADJUSTED[/B] =
CALCULATE (
[InvMovement],
FILTER ( ALL ( 'DATE'[DATE] ), 'DATE'[DATE] <= MAX ( 'DATE'[DATE] ) )
) + [CURRINV]

[B]Most negative ENDING INV UNADJUSTED so far[/B] =
MINX (
FILTER ( ALL ( 'DATE'[DATE] ), 'DATE'[DATE] <= MAX ( 'DATE'[DATE] ) ),
MIN ( [ENDING INV UNADJUSTED], 0 )
)

[B]ENDING INV [/B]=

[B]OPENING INV [/B]=
CALCULATE (
[ENDING INV],
FILTER ( ALL ( 'DATE'[DATE] ), 'DATE'[DATE] < MAX ( 'DATE'[DATE] ) )
)
// I think it's always best to calculate OPENING INV as ENDING INV in the previous period``````
Regards,
Owen

Last edited:

#### gkaropo

##### New Member
Hi Owen,

thanks so much for this. I replicated this on my main dataset and am getting an error. I opened the file that you saved and if i refresh the pivot, the same error comes up.

"The measure 'DATA'[ENDING INV] depends on another measure named 'DATA'[Most negative ENDING INV UNADJUSTED so far] which has an error: Too many arguments were passed to the MIN function. The max argument count for the function is 1.

thanks again...

#### Ozeroth

##### Active Member
Hi again gkaropo,

It sounds like you're using Excel 2013 - I should have checked
In the Excel 2013 version of Power Pivot, the MIN function only works with a single column but not with two scalars .

You just need to change the Most negative ENDING INV UNADJUSTED so far measure as follows (I have done this in my uploaded file as well):
Code:
``````Most negative ENDING INV UNADJUSTED so far =
MINX (
FILTER ( ALL ( 'DATE'[DATE] ), 'DATE'[DATE] <= MAX ( 'DATE'[DATE] ) ),
IF ( [ENDING INV UNADJUSTED] < 0, [ENDING INV UNADJUSTED], 0 )
)``````
Regards,
Owen

#### gkaropo

##### New Member
Owen. You are a rockstar!
thank you!

1,102,643
Messages
5,488,074
Members
407,622
Latest member

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...