Inventory Forecast

[Connerf]

Hey Everyone,

I'm relatively new to DAX & Power BI and could really use some help with the following calculation.

Explanation: I would like to take my current onhand inventory and project it out based on the addition of production schedule and subtracted from the MAX between open orders and forecasted demand.

Challenge: Each day needs to reference the previous days total and then add or subtract the difference between the schedule and open orders.

Variables:

• Production Schedule: how many units that will be made by end of day
• Operating Forecast: daily forecasted orders
• Total Open Qty: total orders that are supposed to be fulfilled that day but haven't been depleted from inventory yet
• Onhand Qty: Current inventory

Current Formulas:

Daily Adjustment = [Total Production Schedule] - MAX([Operating Forecast], [Total Open Qty])

-- This gives me the daily change to inventory before it will happen

Qty On Hand =

IF (

       LASTDATE(dimDate[Date]) > TODAY(),

       BLANK,

       [TOTAL OPEN QTY]
)

-- This returns blank values for days that aren't the current day for Qty On Hand, otherwise Qty On Hand populates every row for every date. Without this formula in place, the Qty On Hand is confusing for users.

INV Total = [Qty On Hand] + Daily Adjustment

-- New inventory total

Inventory Total =

IF(
    SELECTEDVALUE(DimDate[Date]) = TODAY(),
    [INV TOTAL],

    CALCULATE(
           [INV TOTAL],
           DATEADD(DimDate[Date], -1, DAY)
    ) + Adjustment
)

-- My attempt at forecasting our inventory. Which isn't working.

Below is the table I'm working on. The Inventory total amount for March 3rd and 4th are calculating correctly, while dates after that don't work. 

March 5th needs to reference March 4th's total of (887,083) and then add the difference between production schedule and MAX of operating forecast and total open qty.

Inventory Total (March 4th):   887,083

Production Schedule            + 64,116 

MAX(

Operating Forecast               - 51,115

Total Open Qty)                   - 80,968

 ---------------------------------------------

Inventory Total (March 5th)   870,231

What am I doing wrong in my calculation? 

Thanks for your help.

[v-jiascu-msft]

Hi @Connerf ,

The image in your post seems broken. Can you share a sample that we can copy from or download? Please mask the sensitive parts first.

Best Regards,