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.
Our model has an Invoice (Sales Revenue) fact table and a Receipt fact table. The Invoice table has a column showing original sales amount and remaining balance, but does not capture current balance as of any given day. As new payments are received, that outstanding balance is affected, but daily balances are not captured as of any given day. I have set up the data model with the Dates table linked to the Invoice table and the Receipts table linked to the Invoice table by Transaction ID. There is a Receipt date in the Receipt table, but linking the Dates table to the Receipt Date is not allowed due to circular reference.
Is it possible to solve this with changes to DAX and/or data model reconfiguration?
Can a new table be created using SUMMARIZE, ADDCOLUMN, and/or CROSSJOIN (or some combination like this) to always capture and maintain the AR balance as of a certain day without future receipts affecting these values in order to analyze historical DSO trends?
Hopefully, this is clear. Let me know if any further detail is required.
