I was recently asked what initially seemed like an easy question but evolved into quite a puzzler.
Paraphrased, this was the request:
Can I see the Accounts Receivable for the preceding months? At present we record these manually but it would be helpful to look back in time and see what the accounts receivable was at that time.
Here’s how this works:
- The invoice table has an [Invoiced Date] and an [Posted Date].
- The AR for a particular month is the sum of the invoices for which the posted date is null at the end of the month.
While it’s tempting to use OVER expressions such as AllPrevious() or grandious CASE statements. The answer is simple using data limiting:
Month([InvPostDate]) != Month([PmtPostDate])
This removes all the rows for invoices that had their payments post in that month.
When used in a bar chart with the [Invoiced Date] binned by month on X-axis, we aggregate the invoices that pertain to that period.