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)

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago

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's avatar
shuijgen
Frequent Visitor
2 years ago

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