Inventory Tracking and Order Fulfillment

[AnAnalyst]

I'm stuggling with creating a data model and calculations that answer the following questions:

1. What Orders Numbers will I not be able to fulfill in the future due to lack of stock. (Current Stock - Customer Orders + Purchases = Ending Stock)

2. When will I be able to fill those orders based on purchases from suppliers (Back in stock date)?

I've attached a data model with sample data: https://1drv.ms/u/s!Aj5qeXmPp-hpjxbgnSDTmhj7bwi8?e=O92bum

Any help is very much appreciated. 

[grantsamborn]

Hi @AnAnalyst 

Your measure [Sum of Ending Stock] in your model doesn't seem to work.

Would something like this help?

zBalance = 
VAR _CurrentStock =
    CALCULATE(
        SUM( 'Current Stock'[Current Stock] ),
        FILTER(
            ALL( 'Date' ),
            'Date'[Date] <= MAX( 'Date'[Date] )
        )
    )
VAR _CustomerOrders =
    CALCULATE(
        SUM( 'Customer Orders'[Order Quantity] ),
        FILTER(
            ALL( 'Date' ),
            'Date'[Date] <= MAX( 'Date'[Date] )
        )
    ) * -1
VAR _SupplierOrders =
    CALCULATE(
        SUM( 'Supplier Orders'[Quantity] ),
        FILTER(
            ALL( 'Date' ),
            'Date'[Date] <= MAX( 'Date'[Date] )
        )
    )
VAR _Total = _CurrentStock + _CustomerOrders + _SupplierOrders
RETURN
    _Total

Let me know if this helps or if you have any questions.

Added:

Regarding you questons...

1.  Any time [zBalance] is less than 0 would indicate a lack of stock.

2.  I'm not sure of your company's procedures/rules but I think that might take a little more work.

Sorry I had to add this.  After re-reading I realized that you had other requiements besides the running balance.

[grantsamborn]

The above measure will show a line for every date.

You would also probably want a measure like this so that can filter your table.

_Display = IF( ISBLANK( [Sum of Current Stock] + [Sum of Customer Orders] + [Sum of Supplier Orders] ),  0,  1 )

[AnAnalyst]

Let me know if there are any other details I can provide.