Split inventory count on purchase orders

[Powerwoman]

Hello everbody,

hope I will get some help here.
What I need is to identify the latest purchase orders, that provided the inventory count.
Sample data:

Itemno	Inventory Count
1900-S	500
1936-S	90

Purchase orders
Itemno	Units purchased	Inventory units in PO	Remaining Units
1900-S	200	200	300
1900-S	200	200	100
1900-S	200	100	0
1900-S	20	0	0
1900-S	30	0	0
1936-S	13	13	77
1936-S	11	11	66
1936-S	9	9	57
1936-S	5	5	52
1936-S	90	52	0
1936-S	80	0	0

Formula should be like:
First row:
If Inventory Count > Units purchased then Inventory units in PO (1) = Units purchased
Remaining Units (2) = Inventory count - Units purchased
All other rows should use the remaining units, not the inventory count as calculation
The goal is to filter on (1) to get all the POs with inventory values
This should be possible by item

Can this be done in Power BI?

[Powerwoman · Accepted solution]

Hi there,
found a solution:

I created a calculated column that deducts values from the inventory total.

remainingPurchaseUnits =

var _index = itemLedgerEntryPurchase[Index]

var _remainingUnits =  CALCULATE(

    sum(itemLedgerEntryPurchase[amount])

    , ALLEXCEPT(itemLedgerEntryPurchase,itemLedgerEntryPurchase[item])

    ,itemLedgerEntryPurchase[Index] <= _index

)

return

 [inventoryUnits]- _remainingUnits

[amitchandak]

@Powerwoman , Do you have a date column? I think we need to build cumulative, but need date for that

Please share additional columns you have in table

Something like this

Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw