Sales Ledger Aged Debts

[PaulBoyes]

I have two tables, one contains Sales Invoices and the other contains Sales Receipts.  There is a relationship between the tables using the Invoice number.

Any Invoice may have zero, one or many receipts associated.

I want to create a report that a) Calculates each invoice balance by deducting the total of receipts from the invoice amount and b) Displays just those invoices with a non zero balance.

[MattAllington · Accepted solution]

Create a measure for total invoice balance and total receipts using sum. Create another measure for balance by subtracting one from the other. I would wrap this in an if statement to return blank instead of zero 

balance = if ([measure 1] -[measure 2]=0,blank(),[measure 1] -[measure 2])

use the invoice number from sales receipts on rows in a matrix and place balance in values.