Forecasting Opening and Closing Inventory

gkaropo

New Member
Joined
Mar 16, 2019
Messages
5
[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.
link to my file below

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

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

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hello gkaropo

Welcome to the forum :)

I have uploaded an edited version of your file here.

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]=
[ENDING INV UNADJUSTED] - [Most negative ENDING INV UNADJUSTED so far]

[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:
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
I have a similiar problem that I´m not able to solve. Trying to create an inventory forecast. Have starting inventory values for the current month and have a sales forecast and a manufacturing forecast for the next 12 months. There are several different articles, materials, geography which is a difference comparing to above problem, meaning I have many starting inventory values. How can I solve it in powerpivot?
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top