Calculate new stock level

[shuijgen]

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 order line. After delivery of the 61 pieces for this part the new stock level for the next orderline is -13 and so on. How to calculate this in a calc. column?

Delivery date	Partnr	Order Quantity	Voorraad	Index	New Stock level	result from code
07/09/2023	123456	61	48	1477	-13	-13
13/09/2023	123456	47	48	1479	-60	1
20/09/2023	123456	60	48	2895	-120	-12
22/09/2023	123456	29	48	4792	-149	19
29/09/2023	123456	91	48	3761	-240	-43
06/10/2023	123456	46	48	4989	-286	2
20/10/2023	123456	10	48	2765	-296	38
20/10/2023	123456	91	48	2761	-387	-43
27/10/2023	123456	91	48	2769	-478	-43

I have tried this but result is in the right column in above table.

New StockLevel =
VAR _product = 'D_CALCULATIE (2)'[ARTIKELNR]
VAR _date = 'D_CALCULATIE (2)'[Delivery date]
VAR _order = 'D_CALCULATIE (2)'[Order Quantity]
VAR _previousStock = 'D_CALCULATIE (2)'[Voorraad]
VAR _currentStock =
    IF (
        NOT ISBLANK(_date),
        CALCULATE (
            SUM('D_CALCULATIE (2)'[Order Quantity]),
            FILTER (
                ALL('D_CALCULATIE (2)'),
                'D_CALCULATIE (2)'[ARTIKELNR] = _product &&
                'D_CALCULATIE (2)'[Delivery date] < _date
            )
        )
    )
RETURN
    IF (
        ISINSCOPE('D_CALCULATIE (2)'[Index]),
        0,
        _previousStock- _order
        
    )

[AmiraBedh · Accepted solution]

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]

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]

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 · Accepted solution]

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]

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. 

[AmiraBedh · Accepted solution]

You can send it to me (message)

[shuijgen]

i think where it goes wrong. Per partnumber there are more rows with some other info (columns with other data linked). for those rows the stocklevel is 0 in the data table, but the order quantity is the same. in the measure all rows with stocklevel blank should be eliminated.. but how?

[Greg_Deckler]

@shuijgen Try:

Column = 
    VAR __Partnr = [Partnr]
    VAR __DeliveryDate = [Delivery date]
    VAR __MinDate = MINX(FILTER('Table', [Partnr] = __Partnr), [Delivery date])
    VAR __Voorraad = MINX(FILTER('Table', [Partnr] = __Partnr && [Delivery date] = __MinDate), [Voorraad])
    VAR __Table = FILTER('Table', [Partnr] = __Partnr && [Delivery date] <= __DeliveryDate)
    VAR __Delivered = SUMX(__Table, [Order Quantity])
    VAR __Result = __Voorraad - __Delivered
RETURN
    __Result

[shuijgen]

unfortunately not. i get some strange big negative results here

[Greg_Deckler]

@shuijgen It works for your sample data. Is there something else going on with your data model? Or can you provide a larger sample size? See attached PBIX file below that returns the correct results based upon your sample data.

[shuijgen]

thnx Greg. The data model is now running well with the tips i got from Amira, but thnx anyway.