locked
Inventory Forecasting model RRS feed

  • Question

  • 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.


    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]

    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.

    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.

    paste this link to file

    https://1drv.ms/x/s!AgsUyh0i7varnjMfLmnZ1bPgSSGp


    • Edited by GKARO Monday, March 18, 2019 2:25 AM
    Monday, March 18, 2019 2:23 AM

Answers