Forum Discussion

shuijgen's avatar
shuijgen
Frequent Visitor
3 years ago
Solved

Calculate new stock level

I have a table in PowerBi (D_Calculatie (2)) with all kind of partnumbers and customer orders with delivery dates. I have stock level for this part coming from ERP, but is only valid for the first or...
Need Help
Tips and Tricks
  • AmiraBedh's avatar
    AmiraBedh
    3 years ago

    Can you share your data ? Otherwise try the following : 

    New Stock Level = 
VAR _product = 'D_CALCULATIE (2)'[Partnr]
VAR _date = 'D_CALCULATIE (2)'[Delivery date]
VAR _order = 'D_CALCULATIE (2)'[Order Quantity]
VAR _initialStock = CALCULATE(MIN('D_CALCULATIE (2)'[Voorraad]), FILTER('D_CALCULATIE (2)', 'D_CALCULATIE (2)'[Partnr] = _product))

VAR _previousOrders = 
    CALCULATE(
        SUM('D_CALCULATIE (2)'[Order Quantity]),
        FILTER(
            ALL('D_CALCULATIE (2)'),
            'D_CALCULATIE (2)'[Partnr] = _product && 
            'D_CALCULATIE (2)'[Delivery date] < _date
        )
    )

VAR _currentStock = _initialStock - _previousOrders

RETURN
    _currentStock - _order
  • AmiraBedh's avatar
    AmiraBedh
    3 years ago

    You can send it to me (message)

AmiraBedh's avatar
AmiraBedh
Icon for Super User rankSuper User
3 years ago

The key issue with your current calculation is that you are calculating a 'running sum' of orders, but not considering the current order in the calculation. Here's how you can potentially fix it:

Firstly, sort your table by 'Delivery date' and 'Index' to make sure it is in the correct order.

Now, you can create a new column with the following DAX code:

New Stock Level =
VAR _product = 'D_CALCULATIE (2)'[Partnr]
VAR _date = 'D_CALCULATIE (2)'[Delivery date]
VAR _order = 'D_CALCULATIE (2)'[Order Quantity]
VAR _previousStock = 'D_CALCULATIE (2)'[Voorraad]

VAR _sumOfOrdersUpToDate =
CALCULATE (
SUM('D_CALCULATIE (2)'[Order Quantity]),
FILTER (
ALL('D_CALCULATIE (2)'),
'D_CALCULATIE (2)'[Partnr] = _product &&
'D_CALCULATIE (2)'[Delivery date] <= _date // Include current date
)
)

RETURN
_previousStock - _sumOfOrdersUpToDate
  • shuijgen's avatar
    shuijgen
    Frequent Visitor
    3 years ago

    unfortunately not the correct result.

    I would expect that for the first row the result is : -13. Stock =48 and order quantity = 61. So 13 short.

    Next line (in order of delivery date) has than a stock of -13 and if order quantity = 57, the shortage (new stock level) will be -13 -57 = -70. 

     

    from your code i get:

    -196 first line and -380 next line...

    • AmiraBedh's avatar
      AmiraBedh
      Icon for Super User rankSuper User
      3 years ago

      Can you share your data ? Otherwise try the following : 

      New Stock Level = 
VAR _product = 'D_CALCULATIE (2)'[Partnr]
VAR _date = 'D_CALCULATIE (2)'[Delivery date]
VAR _order = 'D_CALCULATIE (2)'[Order Quantity]
VAR _initialStock = CALCULATE(MIN('D_CALCULATIE (2)'[Voorraad]), FILTER('D_CALCULATIE (2)', 'D_CALCULATIE (2)'[Partnr] = _product))

VAR _previousOrders = 
    CALCULATE(
        SUM('D_CALCULATIE (2)'[Order Quantity]),
        FILTER(
            ALL('D_CALCULATIE (2)'),
            'D_CALCULATIE (2)'[Partnr] = _product && 
            'D_CALCULATIE (2)'[Delivery date] < _date
        )
    )

VAR _currentStock = _initialStock - _previousOrders

RETURN
    _currentStock - _order
      • shuijgen's avatar
        shuijgen
        Frequent Visitor
        3 years ago

        When i made a new pbix file with just one partnumber in it and your code I get the correct output. See below. But in the original pbix with more different partnumbers this goes wrong. (see outcome in the column outcome of Amira). When i put an extra partnumber in the text pbix it seems ok. the original data is quite big and confidential. How to upload a test.pbix?

         

        added the line voorraad <> blank() to the script and that did the trick.:

        New Stock Level = 
VAR _product = 'D_CALCULATIE (2)'[ARTIKELNR]
VAR _date = 'D_CALCULATIE (2)'[Delivery date]
VAR _order = 'D_CALCULATIE (2)'[Order Quantity]
VAR _initialStock = CALCULATE(MIN('D_CALCULATIE (2)'[Voorraad]), FILTER('D_CALCULATIE (2)', 'D_CALCULATIE (2)'[ARTIKELNR] = _product))

VAR _previousOrders = 
    CALCULATE(
        SUM('D_CALCULATIE (2)'[Order Quantity]),
        FILTER(
            ALL('D_CALCULATIE (2)'),
            'D_CALCULATIE (2)'[ARTIKELNR] = _product && 
            'D_CALCULATIE (2)'[Delivery date] < _date &&
            'D_CALCULATIE (2)'[Voorraad] <> BLANK()
        )
    )

VAR _currentStock = _initialStock - _previousOrders

RETURN
    _currentStock - _order

        I merged another table to this D_Calculatie table. That created extra rows with same partnumber with their individual sub partnumbers. The current stocklevel from ERP was only filled for the partnumber and not for all sub part numbers. The calculation did the sum of all rows including the sub part numbers where the stocklevel = blank. With this add in the calculation to exclude the blank ones solved the problem.