Setoff Invoices from Receipts – DAX Challenge

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

 

DOWNLOAD THE DATA

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

 

More DAX Brain Teasers!

• The SUM and SUMX DAX Puzzle
• Find Trained Employees who left – DAX Challenge
• Find Unique Dates Challenge
• Calculate Days before the earliest Date

 

---

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