Modification of DAX for Aging Calculation Based on Selected Month

Hi 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,