topic Re: Help for supply chain minimum quantity requirement in DAX Commands and Tips

Help for supply chain minimum quantity requirement

Anonymous — Mon, 02 Dec 2024 19:38:42 GMT

Hello!

I have some difficulties to create the good formula..

My case is this one :

I have some materials which need to have always at a special storage location a minimum of stock quantity (in A01)

If the stock is not covered the minimum quantity. I need to know how many pallets I need toi supply.

When I know how many pallets I need to supply. I check in another storage location how many pallet sI have.

If I have enough quantity in pallet in another storage location, I need to select the batch I need by its oldest creation date (FIFO).

If there are differents lines for the same batch, I just need the batch number. If one batch is not enough to cover my minimum stock, I need to show a 2 batch number until I get enough stock.

For examples in my power BI in attached, for the item 1200952, I have 90 000 pcs in stock in A01 which is not enough because my minimum stock is 145 000 pcs. So I need 55 000 pcs more.

If i check my stock in storage type A02, I see that there is 150 000 pcs.

In the table in my power bi, i can see the details of what I have in stock in A02 per batches.

Here what I would need, it's to show in my first table, in a last column the batches I need to succeed having minimum stock. So it would be batch FA819809/FA819810/ FA819811 in concatenate.

Here is the link of my power bi

https://we.tl/t-ieAjEqhimo

Thank you very much for your help

Manoli

Re: Help for supply chain minimum quantity requirement

lbendlin — Tue, 03 Dec 2024 01:34:47 GMT

Power BI is a reporting tool. It is not suitable for inventory management. Please use a tool that was specifically designed for that purpose.

Re: Help for supply chain minimum quantity requirement

Anonymous — Tue, 03 Dec 2024 06:09:28 GMT

Hello lbendlin

Thank you for your answer

I agree with you but I have no choice, asked me to use Power BI..

Manoli

Re: Help for supply chain minimum quantity requirement

Anonymous — Tue, 03 Dec 2024 06:21:32 GMT

Hi Anonymous ,

Maybe you can try formula like below to create measure:

Deficit = VAR CurrentStock = CALCULATE ( SUM ( 'Stock'[Quantity] ), 'Stock'[Storage Location] = "A01" ) VAR MinimumStock = LOOKUPVALUE ( 'Mini'[Minimum Quantity], 'Mini'[Item], MAX ( 'Stock'[Item] ) ) RETURN MAX ( 0, MinimumStock - CurrentStock )

Batches = VAR RequiredStock = [Deficit] VAR BatchTable = FILTER ( 'Stock', 'Stock'[Storage Location] = "A02" && 'Stock'[Item] = MAX( 'Stock'[Item] ) ) VAR SortedBatchTable = ADDCOLUMNS ( BatchTable, "CumulativeStock", SUMX ( FILTER ( BatchTable, [Creation Date] <= EARLIER ([Creation Date] ) ), [Quantity] ) ) VAR SelectedBatches = CONCATENATEX ( FILTER ( SortedBatchTable, [CumulativeStock] <= RequiredStock ), [Batch], "/", [Creation Date], ASC ) RETURN SelectedBatches

Best Regards,
Adamk Kong

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

Re: Help for supply chain minimum quantity requirement

Anonymous — Tue, 03 Dec 2024 17:57:37 GMT

Hello Adamk

Thank you very much for your proposal!

But I would change the formula like that :

Batches =

VAR RequiredStock = [Deficit]

VAR BatchTable =

FILTER (

'Stock',

'Stock'[Storage Location] = "A02"

&& 'Stock'[Item] = MAX( 'Stock'[Item] )

)

VAR SortedBatchTable =

ADDCOLUMNS (

BatchTable,

"CumulativeStock",

SUMX (

FILTER ( BatchTable, [Creation Date] <= EARLIER ([Creation Date] ) ),

[Quantity]

)

VAR SelectedBatches =

CONCATENATEX (

FILTER ( SortedBatchTable, [CumulativeStock] >= RequiredStock ),

[Batch],

"/",

[Creation Date], ASC

)

RETURN

SelectedBatches

Your formula is showing me a quantity of batch quantity which is inferior to my "deficit" and I need the oposite.

If I change the sign to >= instead of <= the formula does not work complety because I only need to see enough batches by the oldest which can cover at a minimum the quantity in "deficit".

So in reality I would need to see batch FA819809 because of his age (03/02/2024) quantity = 45 000 pcs + FA819810 of 5000 pcs (another batch because we miss 10 000 pcs to have get quantity of 55 000 pcs(in deficit) and his age)

+ FA819811 (55 000 pcs). So I would need to see FA819809 FA819810 FA819811. In quantity is 60 000 pcs, superior of the deficit of 55 000 pcs

Do you have another idea ? 😄

https://we.tl/t-wDzbSs7qZx

Thank you !