Forum Discussion
peterpan
2 years agoHelper I
Amount Adjustment on FIFO basis
I have two tables. Table A-
| Invoice Number | Amount |
| 4001 | 50000 |
| 4002 | 60000 |
| 4003 | 95000 |
| 4002 | 2000 |
Table B-
| Invoice Number | Invoice Item | Invoice Amount |
| 4001 | 18 | 30000 |
| 4001 | 19 | 20000 |
| 4002 | 22 | 26000 |
| 4002 | 23 | 38000 |
| 4002 | 25 | 20000 |
| 4003 | 36 | 80000 |
| 4003 | 38 | 5000 |
I need to add a third column to adjust the amount paid against invoice item amount on FIFO basis as below. Need your assistance-
| Invoice Number | Invoice Item | Invoice Amount | Amount Paid |
| 4001 | 18 | 30000 | 30000 |
| 4001 | 19 | 20000 | 20000 |
| 4002 | 22 | 26000 | 26000 |
| 4002 | 23 | 38000 | 36000 |
| 4002 | 25 | 20000 | 0 |
| 4003 | 36 | 80000 | 80000 |
| 4003 | 38 | 5000 | 5000 |
2 Replies
- AlienSxSuper User
let payments = your_TableA, items = your_TableB, f = (tbl) => [recs = Table.ToRecords(tbl), payment = Record.FieldOrDefault(pr, Text.From(tbl[Invoice Number]{0}), 0), gn = List.Generate( () => [i = 0, paid = List.Min({recs{0}[Invoice Amount], payment}), balance = List.Max({0, payment - paid}), t = recs{0} & [Amount Paid = paid]], (x) => x[i] < List.Count(recs), (x) => [i = x[i] + 1, paid = List.Min({recs{i}[Invoice Amount], x[balance]}), balance = List.Max({0, x[balance] - paid}), t = recs{i} & [Amount Paid = paid]], (x) => x[t] ), back_to_tbl = Table.FromRecords(gn)][back_to_tbl], pg = Table.Group(payments, "Invoice Number", {"p", each List.Sum(_[Amount])}), pr = Record.FromList(pg[p], List.Transform(pg[Invoice Number], Text.From)), pi = Table.Group(items, "Invoice Number", {"pi", each f(Table.Sort(_, "Invoice Item"))}), expand = Table.ExpandTableColumn(pi, "pi", {"Invoice Item", "Invoice Amount", "Amount Paid"}) in expand - FowmySuper User
peterpan
I know you have posted this question under Power Query but I would like to see if you are interested in a DAX solution, if so, add a calculated column in Table B, using the code below :Amount Paid = VAR __InvNum = 'Table B'[Invoice Number] VAR __InvItem = 'Table B'[Invoice Item] VAR __InvAmount = 'Table B'[Invoice Amount] VAR __TotalPayment = SUMX( FILTER( 'Table A' , 'Table A'[Invoice Number] = __InvNum ) , 'Table A'[Amount] ) VAR __CumAmount = SUMX( FILTER( 'Table B' , 'Table B'[Invoice Number] = __InvNum && 'Table B'[Invoice Item] <= __InvItem ) , 'Table B'[Invoice Amount] ) VAR __RemAmount = __TotalPayment - __CumAmount RETURN IF( __RemAmount >= 0 , __InvAmount , MAX( 0, __InvAmount + __RemAmount ) )Output: