Off late I got to work on an interesting accounting problem and I wanted to see if you can work out a better solution (I am sure there is). Not wasting anymore time, let’s dive right in

### Consider this Invoices Data..

1. One row means one unique invoice.
2. Rest of the columns are pretty self explanatory.

### Now Consider this Receipts Data

1. One row means one instance of payment received.
2. The receipts from customers are never the against any specific invoice.
3. For a particular customer the receipts may exceed the amount billed. For example, Customer A’s total of all invoices = 100 but total receipts could be = 120

### The Problem

You are required to create a report with 5 fields

• Date
• Invoice Number
• Amount Billed – Measure
• Receipts Allocated – Measure
• Balance – Measure

Filters – One should be able to filter this report by Customer or Month Ending Date.

How should the measures work

1. Amount Billed – The total value of the Invoice (pretty straight forward).
2. Receipts Allocated – Since receipts are not specific to Invoices you’ll have to allocated the receipts against invoices using FIFO method.
3. Balance – Is the amount pending against the invoice. It should be blank in case the receipts are more than the invoice amount

### Sample Output

You can see that

• The entire table is working on 2 filters – As on Date and Customer
• The Receipts are allocated to Invoices as per FIFO
• The Balance becomes 0 (or blank) when the receipts till date exceed the invoiced amount till date
• You can forget about the tick mark (✔️) for now :D. that’s just an additional brownie

### Rules

• You can solve this using Query or DAX or a mix of creative Data Modeling & DAX using Power BI or PowerPivot but NO excel formulas / vba
• Comment your answers and post your solutions with a link (to download the PBI / Excel file). That’s just helpful in-case a co-reader would like to play with your solution

Consider this Solution – Have created a plug and play template, ready to use!

### More DAX Brain Teasers!

PS: Since I am writing this as the first blog of the year 2020. If I am not too late wish you a very happy new year! Awaiting your comments