Forum Discussion
Accounts Receivable Closing
Hello,
I am stuck in preparying an Accounts Receivable report by customer and by invoice. I have two columns "Invoice Amount" and "Closing Amount". What I want to do is to match the amount in the column Closing Amount with the Invoice amount and to add another column which to give me as a result only the remaining Invoice Amount. I import the information in Power BI from Dynamics NAV database, where each payment is posted with a number totally different from the invoice number, I have a couple of columns which I think may help me but still stuck cannot think of an approach. The columns are: "Entry No._" and "Closed by Entry No._" but I still cannot figure out how to match these. I am thinking something like: IF "a number from column Closed by Entry No._" is FOUND in column "Entry No._" then RETURN the "value from column Payments"; OTHERWISE "Leave blank". Can anyone help me to convert this in formula?
Can you try a new column in Payments Table:
IF (Payments_Table[Closed_by] IN Values(Sales[Entry_No]), Payments_Table[Payments],0)