Modification of DAX for Aging Calculation Based on Selected Month

[InsightSeeker]

I have created the following DAX to calculate the aging as of TODAY. Now, I need to modify this DAX to calculate the aging as of the Selected Month (Last Day of the month). How can I accomplish this?

When I click on the month, such as Dec 23 in the slicer, the aging should change based on the last day of the month for Dec 23, which will be December 31st. Any due date after December 31st, 2023, should be in the current bracket, and any due date on or before should fall within the months defined in the DAX.

When I change and click on the month, such as Mar 24 in the slicer, the aging should change based on the last day of the month for Mar 24, which will be March 31st, 2024. Any due date after March 31st, 2024, should be in the current bracket, and any due date on or before should fall within the months defined in the DAX.

Would it be better to achieve these results through a calculated column or a slicer?

Attached is the Sample Report.  Click Here

Aging =

var _datediff = DATEDIFF(Due_Date[Due_Date], TODAY(),MONTH)

RETURN SWITCH(TRUE(),

    _datediff <= 0,"Current",

    _datediff = 1,"1 Month",

    _datediff = 2,"2 Month",

    _datediff = 3,"3 Month",

    _datediff >= 4 && _datediff <= 6,"4-6 Month",

    _datediff >= 7 && _datediff <= 9,"7-9 Month",

    _datediff >= 10 && _datediff <= 12,"10-12 Month",

    _datediff >12,"Above 12 Month",

    "0")

[DataNinja777]

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, 

[InsightSeeker]

Hello @DataNinja777  Please find below the link to my data. 

Click Here

[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

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

[jdbuchanan71]

In your model you linked the caledar table to the invoice date so when you select a month you are only looking at invoices in that month.  That doesn't allow for the dynamic behavior you are looking for.  The due date will always be later than the month you selected (due date is always after invoice date).

[InsightSeeker]

@jdbuchanan71  - Even if i change my model from inv date to the due date, still I don't get the desired results. My result always show 0 for all.

[jdbuchanan71]

Based on your selection of Aug-23, what would you want to see in the aging column? It's not clear to me what you are trying to accomplish.

[InsightSeeker]

Hi @jdbuchanan71  - When you click on Aug '23, the aging should be calculated from the due date as of the last day of Aug '23, and it should display all transactions. When the slice is used, only the aging is changed based on the month selected. It should display all transactions.

Criteria:
- If you have selected Aug '23, the result should be:
- If the month difference between the due date and Aug '23 is 0 or less, it should be in the "Current" bracket.
- If the month difference between the due date and Aug '23 is 1, 2, or 3, it should be in the "1 Month" bracket.
- If the month difference between the due date and Aug '23 is equal to 4 or less than or equal to 6, it should be in the "4-6 Month" bracket.
- If the month difference between the due date and Aug '23 is equal to 7 or less than or equal to 9, it should be in the "7-9 Month" bracket.
- If the month difference between the due date and Aug '23 is equal to 10 or less than or equal to 12, it should be in the "10-12 Month" bracket.
- If the month difference between the due date and Aug '23 is greater than 12, it should be in the "Above 12 Month" bracket.

I hope the above is clear. Please feel free to let me know if you need more information.

[jdbuchanan71]

@InsightSeeker 

You can't do it as a calculated column because those are only updated when the model is refreshed, not when a user makes a selection on a slicer.  You can also simplyfy your SWITCH a bit by starting from the top.

Aging Measure = 
VAR _EndDate = MAX('Calendar Ultimate'[Date])
VAR _Due_Date = SELECTEDVALUE(Due_Date[Due_Date])
var _datediff = DATEDIFF(_Due_Date, _EndDate,MONTH)
RETURN SWITCH(TRUE(),
    _datediff >12,"Above 12 Month",
    _datediff >= 10, "10-12 Month",
    _datediff >= 7,"7-9 Month",
    _datediff >= 4,"4-6 Month",
    _datediff = 3,"3 Month",
    _datediff = 2,"2 Month",
    _datediff = 1,"1 Month",
    _datediff = 0,"Current",
    _datediff < 0, BLANK(),
    "0")

[InsightSeeker]

Hi @jdbuchanan71  - When i try to use the same measure in my data it doesn't work. 

Also, I have slightly changed my data and here is the updated file.

Click Here