topic FIFO question in DAX Commands and Tips

FIFO question

JaviGoldstein — Fri, 23 Feb 2024 17:24:46 GMT

Hi, I am trying to "assign" pending orders to batches based on FIFO. I have 2 tables:

tblSKU

tblPendingOrders

I want to add a column to tblSKU showing pending orders. So, with the current pending orders, tblSKU would look like this:

Looking for the best way to accomplish this with DAX. Thx!

Re: FIFO question

lbendlin — Sat, 24 Feb 2024 01:21:53 GMT

Please use a proper logistics planning tool. Power BI has no memory and is incapable of handling material allocation tasks. Its focus is on reporting data.

Re: FIFO question

Anonymous — Mon, 26 Feb 2024 06:19:42 GMT

Hi JaviGoldstein ,
Here us my test data:

Create a calculate column

Pending Orders = VAR tblPO = CALCULATE( MAX(tblPendingOrders[Amount]), FILTER( tblPendingOrders, tblPendingOrders[SKU] = tblSKU[SKU] ) ) VAR temp_result = IF( (tblPO-tblSKU[Balance]) >= 0, tblPO-tblSKU[Balance], 0 ) RETURN IF( (temp_result - tblSKU[Balance]) > 0, temp_result - tblSKU[Balance], IF( tblPO > tblSKU[Balance], IF( tblSKU[Batch] = 1, tblSKU[Balance], temp_result ), temp_result ) )

Final output

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: FIFO question

JaviGoldstein — Tue, 27 Feb 2024 14:09:32 GMT

Anonymous this is perfect. THANKS!

Re: FIFO question

JaviGoldstein — Tue, 27 Feb 2024 14:10:48 GMT

not using this as a logistics solution. Reporting only.