topic Re: Aging Bucket With One Date Column in DAX Commands and Tips

Aging Bucket With One Date Column

kballar1 — Sun, 28 Apr 2024 23:26:20 GMT

Hello,

I am creating a dashboard that needs to meet these requirements:

Visuals for both Receivables and Payables showing individual buckets. Sorting should be based on age:

Current

0-30

31-60

61-90

91-120

Attached is a snippet is the only data I have to work with. Dates range from Feb 2024-May 2024. I keep creating an aging bucket using this measure but it only shows up the 0-30 day bucket. Please help.

Age_Bucket = 
VAR _Age = DATEDIFF('Table'Due Date,'Table'[Due Date], DAY)
VAR _Result = 
SWITCH(
    TRUE(),
    _Age < 30, "0-30 days",
    _Age >= 31 && _Age < 60, "31-60 days",
    _Age >= 61 && _Age < 90, "61-90 days",
    _Age >= 91, "120+ days"
)
Return
_Result

Re: Aging Bucket With One Date Column

lbendlin — Mon, 29 Apr 2024 00:42:48 GMT

Age_Bucket = VAR _Age = DATEDIFF('Table'[Due Date], TODAY(), DAY) RETURN SWITCH( TRUE(), _Age < 30, "0-29 days", _Age < 60, "31-59 days", _Age < 90, "60-89 days", "90+ days" )

Re: Aging Bucket With One Date Column

aduguid — Mon, 29 Apr 2024 04:33:49 GMT

Calendar Aging = VAR _today_date = TODAY() VAR _min_date = CALCULATE(MIN(YourTable[Due_Date])) VAR _result = UNION ( ADDCOLUMNS (CALENDAR ( _today_date - 30, _today_date - 1), "Aging Days", "0 to 30", "Aging Days Order", 1) , ADDCOLUMNS (CALENDAR ( _today_date - 60, _today_date - 31 ), "Aging Days", "31 to 60", "Aging Days Order", 2) , ADDCOLUMNS (CALENDAR ( _today_date - 90, _today_date - 61 ), "Aging Days", "61 to 90", "Aging Days Order", 3) , ADDCOLUMNS (CALENDAR ( _today_date - 120, _today_date - 91 ), "Aging Days", "91 to 120", "Aging Days Order", 4) , ADDCOLUMNS (CALENDAR ( _min_date, _today_date - 121), "Aging Days", "120+", "Aging Days Order", 5) ) RETURN _result

Re: Aging Bucket With One Date Column

Anonymous — Mon, 29 Apr 2024 06:27:27 GMT

Hi kballar1

Thanks for the reply from lbendlin and aduguid , please allow me to provide another insight:

Do you want to compare the maximum date in a table with the date of each row? If so, try the following formula.

Age_Bucket = VAR _MAX = CALCULATE(MAX([Due Date]), ALL('Table')) VAR _Age = DATEDIFF(MAX([Due Date]), _MAX, DAY) VAR _Result = SWITCH( TRUE(), _Age < 30, "0-30 days", _Age >= 31 && _Age < 60, "31-60 days", _Age >= 61 && _Age < 90, "61-90 days", _Age >= 91, "120+ days" ) Return _Result

I added the dates through May to your screenshot.

Result:

If I've misunderstood you, please feel free to let me know.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.