Below is a sample of the scenario I would like to build and need your support. The story goes like this:
I have got all of the Sales Invoices (Debit) and Sales Receipts (Credit). The first answer that I need is easy. I would like to know what is each customer’s total outstanding balance. I have got that by simply saying Debit minus Credit. Given that I have got a list of customers, I can see what the outstanding per customer is.
However, here is the challenge which I dont know how to solve. In the data, there are customers who have go more than one sales invoice and/or receipts. As such, I would like to be able to breakdown the Total Outstanding Balance and see which specific invoice is paid and which one is still outstanding.
I would like to apply the receipt to the oldest invoice first and then to the second oldest, etc.
So, in order to see that, I would like a column that tell me if an invoice is “Paid”, “Part-Paid” or “Outstanding” and ideally, another column that shows me the outstanding balance per invoice.
I would be grateful for any assistance on this.
Here is the file for your reference. Sample.pbix (1.0 MB)