Forum Discussion
Calculate new stock level
- 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 - 3 years ago
You can send it to me (message)
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 - _orderI 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.
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?