Split inventory count on purchase orders

Question

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:ItemnoInventory Count1900-S5001936-S90&nbsp;Purchase orders&nbsp;&nbsp;ItemnoUnits purchasedInventory units in PORemaining Units1900-S2002003001900-S2002001001900-S20010001900-S20001900-S30001936-S1313771936-S1111661936-S99571936-S55521936-S905201936-S8000&nbsp;Formula should be like:First row:If Inventory Count &gt; Units purchased then Inventory units in PO (1) = Units purchasedRemaining Units (2) = Inventory count - Units purchasedAll other rows should use the remaining units, not the inventory count as calculationThe goal is to filter on (1) to get all the POs with inventory valuesThis should be possible by itemCan this be done in Power BI?

powerwoman · Accepted Answer

Hi there,found a solution:I created a calculated column that deducts values from the inventory total.remainingPurchaseUnits =var _index = itemLedgerEntryPurchase[Index]var _remainingUnits = &nbsp;CALCULATE( &nbsp; &nbsp; sum(itemLedgerEntryPurchase[amount])&nbsp; &nbsp; , ALLEXCEPT(itemLedgerEntryPurchase,itemLedgerEntryPurchase[item])&nbsp; &nbsp; ,itemLedgerEntryPurchase[Index] &lt;= _index)return&nbsp;[inventoryUnits]- _remainingUnits&nbsp;

Forum Discussion

Split inventory count on purchase orders

2 Replies

Related content

Inventory and Stock balance calculation

Inventory Forecast

Inventory Management Dashboard

Inventory opening closing

Microsoft fabric license purchase

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