A seemingly complex task...

[CoreyP]

Hey all,

I've got what might be a fun one for all you experts. Here's what I'm trying to do:

If any of you have a background in warehouse management, you might get this right off the bat. I'm essentially trying to come up with a way to automate a pick sequence. What I want to do, is filter my inventory table to display the pallets that need to be picked, in order, until the ordered quantity is met. 

In the following picture, you can see my example. So, table on the left is from my orders table. It has product and quantity ordered. The table on the right is inventory. When I select the product on the left, I want the table on the right to return all the records up until that black arrow. ( It just happens to be the total of ordered quantity, or 59,400. ) Now, if the ordered quantity were 60,000, I'd want the table to return the next row, even though the total would be 60,840. So, it has to be filtered to AT LEAST the total for ordered quantity, or, all records that exist if we don't have more than ordered. Now, the kicker is this has to be picked in sequence, and we pick by pallet. So, I think whatever happens, I need to be able to rank pallet id by Lot_no first, which is sequential, and then by FIFO_date. This would essentially create a pick sequence by pallet number for each product ordered. ( Note, Inventory and Orders table are connected via 3rd dimTable on Item_no )

I hope I explained this correctly. If not, I can try to build a sample *.pbix with fake data as I'm using sensitive information. Thanks for all the help! =D

[kentyler]

After thinking about this for while, it seems like a problem that should be solved in part in the transaction system, rather than in power bi. I've built picklist systems, when the order systems needs to generate a picklist there should be a set of records that attach to the order that list the pallets that were "picked"
After the pick it sounds like the number of items on some pallets will need to be adjusted and that probably should be done in the transactional database as well. Because the next order for the same item will need to know that the previously picked pallets are no longer available.

I realize you might not have that kind of choices about which systems does which actions... but it's an interesting question to think about,

[CoreyP]

We're running a pretty advanced WMS with a lot of a features.

What this report will be used for is to generate a list of product for a planned inventory move from overstock locations to foward pick locations. Essentially, they want to move enough product from overstock locations to floor locations, the night before they start picking the orders, so that the pickers aren't held up by replenishments and don't have to pick from overstock.

[kentyler]

What you are describing is quite doable...but you are right, its complex. Would you like to do a video session and look over the steps you need to take ? It would be much easier that going back and forth in email.

Email me ken@8thfold.com with a good time to talk and I'll send you a Zoom invitation.

Power BI is easy to learn, but it has hard parts. I'm a personal Power Bi Trainer I learn something every time I answer a question

[CoreyP]

That would be extremely helpful, however, I'm limited in sharing data since it's all confidential information. 

Do you mind if we just go step by step here? 

I think the first thing we need to do is create a measure for Pallet Pick Sequence, which would be a RANKX measure of Pallet_ID, ranked first by minimum value in LOT_NO, then minimum value in FIFO_DATE. Correct?

[kentyler]

I don't know if you need a ranking... i think just sorting the table would give you want you want. What you are going to need is some sort of index field to use in doing the running total. It's possible you could use the ranking as an index to step to the "next" pallet when you are doing the running total.

[CoreyP]

**edit**

In researching cumulative totals, I do see we would need an index column. And yes, for this situation we would use the Pallet Rank as our index column.

[kentyler]

A measure will need some way of calculating its "previous" row, as there is no built in concept of row numbers in power bi. Sometimes people use dates for this, often they use an index column.

An index column would imply that all your pallets were sorted in the correct order.

Then your measure can do a sum() of all the rows with the index > the row it is executing on

which sort of implies that you have filtered the records to contain pallets with that part

[CoreyP]

I created a "Pallet Ranking" by going into query editor, and sorting first by SKU, then LOT, then FIFO date, and adding an Index column. 

Seems to work. So how would I create the running total measure? I tried the following but it did not work:

Cumulative Qty =

CALCULATE (

SUM( 'Inventory'[ONHAND_QTY] ),

FILTER (

ALL ( 'Inventory'[Pallet Rank] ),

'Inventory'[Pallet Rank] <= MAX ( ( 'Inventory'[Pallet Rank] ) )

)

)

[kentyler]

I think you want to store the current value before you do calculate

 

Cumulative Qty =

var  cur_index = max('inventory'[Pallet rank])

 

CALCULATE (

SUM( 'Inventory'[ONHAND_QTY] ),

FILTER (

ALL ( 'Inventory'[Pallet Rank] ),

'Inventory'[Pallet Rank] <= cur_index )

)

)

[kentyler]

If you just return the current index, does it show the correct number ?

[CoreyP]

This is what it returns:

Using this measure:

Cumulative Qty =

VAR cur_index = MAX('Inventory'[Pallet Rank])

RETURN

CALCULATE (

SUM( 'Inventory'[ONHAND_QTY] ),

FILTER (

ALL('Inventory'[Pallet Rank]),

'AIRMInventory'[Pallet Rank] <= cur_index ))

[kentyler]

Try moving the ALL() outside the FILTER()

[kentyler]

and you can short cut the filter statement 

FILTER (

ALL('Inventory'[Pallet Rank]),

'AIRMInventory'[Pallet Rank] <= cur_index ))

by just writing

, 'AIRMInventory'[Pallet Rank] <= cur_index

as an argument to CALCULATE

CALCULATE will turn it into a filter automatically

[CoreyP]

Still not making any progress...pretty sure this link describes something very similar for what I'm trying to do, just can't get it to work in my context.

https://forum.enterprisedna.co/t/running-totals-on-a-measure-with-no-index-or-date-column/147/7

[kentyler]

I know the feeling. It can't seem impossible. There is just some little detail we have to find.

If you just write the Rankx measure (following his example) in a measure and put it in a table, what does it return ?

[CoreyP]

That doesn't seem to be working. Just returns 1 for every value. Do we have to take item_no into context somehow?

[kentyler]

So

If your orders were in a relationship to your inventory.

And you created a slicer for your orders.

When you selected an order in the slicer.

It would filter your inventory.

If you had your inventory table sorted by pallet id by Lot_no first, which is sequential, and then by FIFO_date, then you could create a "running total" for each product, and you would be able to see how many pallets you needed to pick to hit the number needed by the order.

Then the question is, how do you want to transmit/print the pick list to the warehouse ?  

[CoreyP]

Yes, we also need to create a running total measure as well, so that we can compare to ordered quantity.

And yes, I have a relationship between orders and inventory via an item_no master table.