Forum Discussion
Helper IIIModification of DAX for Aging Calculation Based on Selected Month
Super UserHi InsightSeeker ,
I couldn't see the sample file in your link anymore becasue it appears to have been removed. Anyway, without looking at the sample file, I am assuming that you are trying to prepare accounts receivable ageing report or something like that. Please let me know if this is not the case. If you need to prepare the ageing profile of the snap shot at points in time in the past, you need to download either the following in raw data.
- all the AR transaction lists with the customer number and posting date and net due date in the AR module for the past historical record, or
- open item lists by customers with posting dates and net due dates from the AR module at every month end dates in your analysis period.
The benefit of downloading the open item lists as opposed to downloading all the transaction lists is that since your analysis is focused on what is the open item ageing list at point in time, you can reduce the volume of data in this way, but the number of times you need to download will be as many as your month end dates you want to analyze, so some repetitive download activity from your ERP will be required.
Although I couldn't see your data, I am assuming that your data is like the 1st option of all the transaction data, as open item lists will not be able to cover the past cleared off balances which don't exist in your data.
Becasue your ageing needs to refer to the selection of your slicer, you cannot use calculated column approach in this case, becasue it will fix the number of days of ageing at any specific point in time.
Also, if you are using the all the transaction data covering all the period, your balance sheet accounts receivable balance at any point in your selection is the cumulative sum of the all the sales and cash collections from the inception recorded in your GL account using the posting date.
There's an article which discuss the ageing calculation using the open item list, (not the 1st type of data). Using open item list is easier than using the all the historical transaction data. However, for multi-reporting period analysis, you need to download open item lists at each reporting period and combine in one file, adding identification field like reporting date field to indicate which date to base the calculation of the ageing on (i.e., net due date minus reporting date). Because ageing is always with respect to the reference date, as time is ticking all the time, ageing yesterday is not the same as ageing today ⏲.
https://p3adaptive.com/reducing-dso-using-power-pivot/
Best regards,
InsightSeeker- DataNinja777
Thank 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