Forum Discussion

ganchevd's avatar
ganchevd
Helper I
8 years ago
Solved

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?

  • anandav's avatar
    anandav
    8 years ago

    Can you try a new column in Payments Table:

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

9 Replies

  • anandav's avatar
    anandav
    Skilled Sharer

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

    • ganchevd's avatar
      ganchevd
      Helper I

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

       

       

       

      • anandav's avatar
        anandav
        Skilled Sharer

        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.