Ageing table in DAX
Calendar Ageing =
VAR _today_date = TODAY() //'Properties'[Today Date]
VAR _min_date1 = CALCULATE(MIN(Table1[Date1]))
VAR _min_date2 = CALCULATE(MIN(Table2[Date2]))
VAR _future_date = IF(_min_date1 < _min_date2, _min_date1, _min_date2)
//VAR _future_date = DATE( YEAR( _today_date ) - 6, 01, 01 )
VAR _result =
UNION (
ADDCOLUMNS (CALENDAR ( _today_date - 30, _today_date), "Ageing Days", "0 to 30", "Ageing Days Order", 1)
, ADDCOLUMNS (CALENDAR ( _today_date - 60, _today_date - 31 ), "Ageing Days", "31 to 60", "Ageing Days Order", 2)
, ADDCOLUMNS (CALENDAR ( _today_date - 90, _today_date - 61 ), "Ageing Days", "61 to 90", "Ageing Days Order", 3)
, ADDCOLUMNS (CALENDAR ( _today_date - 120, _today_date - 91 ), "Ageing Days", "91 to 120", "Ageing Days Order", 4)
, ADDCOLUMNS (CALENDAR ( _future_date, _today_date - 121), "Ageing Days", "120+", "Ageing Days Order", 5)
)
RETURN
_result
