Forum Discussion

AnAnalyst's avatar
AnAnalyst
Icon for Helper III rankHelper III
3 years ago

Inventory Tracking and Order Fulfillment

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. 

Need Help
Tutorial Requests

3 Replies

  • grantsamborn's avatar
    grantsamborn
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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's avatar
      grantsamborn
      Icon for Solution Sage rankSolution Sage
      3 years ago

      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 )