Forum Discussion
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 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
)
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 - _orderYou can send it to me (message)
10 Replies
- AmiraBedh
Super User
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 - _sumOfOrdersUpToDateunfortunately 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
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
- Greg_Deckler
Community Champion
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 __Resultunfortunately 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.
