Comparing Historical DSO (Days Sales Outstanding)

In calculating DSO (Days Sales Outstanding) to analyze Accounts Receivable collection efficiency, I would like to compare current DSO data with historical DSO data to determine positive or negative trends in collection efficiency. The primary issue - the current accounts receivable balance is always changing based on payments received and with our current data model, we do not have static historical Accounts Receivable balances as of any given day in the past (similar to what inventory stock control models may have).

In order to analyze historical DSO figures, I need to be able to capture what the accounts receivable balance was as of any given day and hold that historical value for use with the standard DSO calculations.

We are using this calculation:

DSO 90 =

Total Accounts Receivable /

( Total Revenue in Past 90 Days / 90 Days )

This works well, but on a line graph, for past dates, the DSO always looks better than it actually was were we to have captured what the DSO was on that given date without any future receipts affecting the AR balance.

For example, if we have an invoice from 2 weeks ago, but the payment was not received until today, the DSO when calculated 2 weeks ago would be different than when we calculate the DSO for that date now.