FIFO Allocation with a fixed amount

shuyin

New member
Joined
Aug 11, 2016
Messages
26
Reaction score
0
Points
0
Excel Version(s)
2016
hi guys,

I have 2 tables:

1 with the max allocation:

CustomerAllocation
A8
B7
C5

1 with required from each customer, and date:

CustomerDateRequired
A07-08-164
A08-08-165
B09-08-166
C10-08-167
A11-08-168
B12-08-169

I want to allocate the Allocation according to the date FIFO of each customer. Result table looks like:
CustomerDateRequiredAllocated
A07-08-1644
A08-08-1654
B09-08-1666
C10-08-1675
A11-08-1680
B12-08-1690

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
 

Attachments

  • FIFO Allocation.xlsx
    154.8 KB · Views: 118
Last edited:
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.
 
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
 
Back
Top