Forum Discussion

peterpan's avatar
peterpan
Helper I
2 years ago

Amount Adjustment on FIFO basis

I have two tables. Table A-

Invoice NumberAmount
400150000
400260000
400395000
40022000

Table B-

Invoice NumberInvoice ItemInvoice Amount
40011830000
40011920000
40022226000
40022338000
40022520000
40033680000
4003385000

 

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 NumberInvoice ItemInvoice AmountAmount Paid
4001183000030000
4001192000020000
4002222600026000
4002233800036000
400225200000
4003368000080000
4003385000

5000

2 Replies

  • peterpan 

    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
  • 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: