Accounts Receivable Closing

[ganchevd]

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?

[anandav]

Can you post some sample data please (not real data but proper columns)?

[ganchevd]

Here is some data, but i was only available to share it as PNG, not table format.

[anandav]

@ganchevd,

There is lot more details missing in your data like no Payments or Invoiced Amount columns.

But based on your your IF condition, you can achieve that as below.

Without understanding more details of the data and the expected output I'm unable to add more value.

[ganchevd]

Thanks. I've noticed what you mean and I see that there is no Invoiced Amount Column, which is in another table, which I obtain from the database. The Payments column is in the attached snapshot but it's called Closed by amount (LCY). So now it is becoming more chalenging. The formula I need is: IF (Payments_Table[Closed_by] is found in (Sales[Entry_No]), Payments_Table[Payments],0).

I still try to find it with no success neither as a "New Column", nor as "New Measure".

[anandav]

@ganchevd,

How is Payments_Table and Sales table related (which is one and which is many)? Is this relationship in Power BI as well?

[ganchevd]

They are related "One to One", "Both"

[anandav · Accepted solution]

Can you try a new column in Payments Table:

IF (Payments_Table[Closed_by] IN Values(Sales[Entry_No]), Payments_Table[Payments],0)

[ganchevd]

Works! Works! Thanks!

[anandav]

Enjoy Power BI