Forum Discussion
Aging Bucket With One Date Column
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
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" )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 _resultHi 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 _ResultI added the dates through May to your screenshot.
Result:
If I've misunderstood you, please feel free to let me know.
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
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" )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 _resultHi 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 _ResultI added the dates through May to your screenshot.
Result:
If I've misunderstood you, please feel free to let me know.
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.