FIFO Allocation with a fixed amount

[shuyin]

hi guys,

I have 2 tables:

1 with the max allocation:

Customer	Allocation
A	8
B	7
C	5

1 with required from each customer, and date:

Customer	Date	Required
A	07-08-16	4
A	08-08-16	5
B	09-08-16	6
C	10-08-16	7
A	11-08-16	8
B	12-08-16	9

I want to allocate the Allocation according to the date FIFO of each customer. Result table looks like:

Customer	Date	Required	Allocated
A	07-08-16	4	4
A	08-08-16	5	4
B	09-08-16	6	6
C	10-08-16	7	5
A	11-08-16	8	0
B	12-08-16	9	0

In excel, the approach is to sort the customer, date, then use the running total for each customer, and then compare with the allocation quantity.

What is the approach in Power Query to resolve this one? We can mimic the Excel approach with recursive function in Power Query, but it seems very ugly.

Thanks

 

[Ken Puls]

I'm somewhat confused by this. To me, FIFO is "First in First Out" and is related to inventory. This doesn't look like the same kind of calculation here, but rather an inventory tear-down with a maximum order quantity for any customer.

If that's the case, where I'm struggling with is why customer B doesn't get 1 unit on the 8/12/2016 line. They have a max allocation of 7 units, but only ordered 6 on 8/9/2016.

 

[shuyin]

hi Ken,

Thank you for your feedback. You are right, customer B should have received 1 unit on 8/12/2016. It is my bad making the mistake.

By FIFO, I meant that we follow the date sequence to allocate the inventory to each customer requirement.

Br, T