Forum Discussion
Helper IIIModification of DAX for Aging Calculation Based on Selected Month
Helper III
Super UserThank you InsightSeeker for sharing the sample data with me.
I have a couple of observations.
1) Your 'Calendar Ultimate' table doesn't cover the entire period of the invoice date, and because of this, not all the data in fact table is slicerable from 'Calendar Ultimate' table. In order to counter this issue, I've created a calculated calendar table which covers all the period of the invoice dates.
2) Another observation about your data is that your 'Data' only contains the Invoice information only and not the cash collection. In fact, the construct of the AR balance is "Invoice recording minus cash collection" for the balance sheet. So unlike the P&L sales amounts which just accumulates over the year to date period, the balance of the balance sheet accounts receivable is the cumulative total from the inception adding the invoice and subtracting the cash collection. Without the cash collection information, you cannot say if the invoice is still outstanding or cleard by the cash collection at any particular point in time. Because of this, from your sample data we will just be able to see invoice ageing at particular point in time without incorporating the 2nd part of the story if that invoice is still outstanding at that particular point in time to begin with. Or are those invocie still outstanding and not collected yet?
I will carry on with the 2nd assumption, but I'd like to check with you these points first.
Best regards,
- InsightSeeker
Hi DataNinja777 - Thank you for helping and looking into this.
For point 1, I didn’t realize that the 'Calendar Ultimate' table doesn't cover the entire period. I will correct this now.
For point 2, my data originally looks like the example below, but in the data I shared with you, I have only provided the Due Amount (Balance).
Customer_Code Inv_Nbr Inv_Date Inv_Amt Paid_Amt Due_Amount Submission_Date Due_Date 123456 12324 1-Jul-23 2510 2,510 - 01-Aug-23 01-Oct-23 126001 32313 2-Jul-23 2860 2,860 - 17-Aug-23 17-Aug-23 123456 21212 1-Jul-23 2510 - 2,510 01-Aug-23 01-Oct-23 126001 1212 2-Jul-23 2860 - 2,860 17-Aug-23 17-Aug-23 - DataNinja777
Hi InsightSeeker ,
OK. The picture is now clearer. In order to get a information as to the aging of the accounts receivale at any particular point in time, you need to utilize the invocie amount, invocie date, and paid amount and paid date fields instead of the due amount column, which is dependent on particualr date status. If we just base our analysis on the [Due_Amount] column, our analysis will be restricted to the due amount as of the date the data was extracted, and we cannot perform flexible analysis for multiple periods because that fact table figures you provided are only providing the status of the AR as of the particular date which the data was downloaded. So, in order to perform the correct analysis over multiple periods instead of just as of the date the data was extracted, we also need to utilize Invoice amount and Paid amount rather than just Due_Amount information.
Best regards,