Aging Bucket With One Date Column

[kballar1]

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   

[lbendlin · Accepted solution]

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"
)

[aduguid · Accepted solution]

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

[v-xuxinyi-msft · Accepted solution]

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.

[aduguid · Accepted solution]

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

[lbendlin · Accepted solution]

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"
)