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..
- One row means one unique invoice.
- Rest of the columns are pretty self explanatory.
Now Consider this Receipts Data
- One row means one instance of payment received.
- The receipts from customers are never the against any specific invoice.
- 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
You are required to create a report with 5 fields
- 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
- Amount Billed – The total value of the Invoice (pretty straight forward).
- Receipts Allocated – Since receipts are not specific to Invoices you’ll have to allocated the receipts against invoices using FIFO method.
- Balance – Is the amount pending against the invoice. It should be blank in case the receipts are more than the invoice amount
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
- 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!
- 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