Timeframe table in DAX
Calendar Timeframe =
VAR _today_date = TODAY() //'Properties'[Today Date]
VAR _yesterday_date = _today_date - 1
VAR _week_start = _today_date - WEEKDAY ( _today_date, 2 )
VAR _week_end = _today_date - WEEKDAY ( _today_date, 2 ) + 6
VAR _month_start = DATE( YEAR(_today_date), MONTH(_today_date), 01 )
VAR _month_end = EOMONTH( _today_date, 0)
VAR _quarter_start = DATE ( YEAR (_today_date), ROUNDUP ( DIVIDE ( MONTH (_today_date), 3 ), 0 ) * 3 - 2, 1 )
VAR _quarter_end = EOMONTH(EDATE(_quarter_start, 2), 0)
VAR _fiscal_year = YEAR(EDATE( _today_date, 6))
VAR _fiscal_year_start = DATE( _fiscal_year - 1, 07, 01)
VAR _fiscal_year_end = DATE( _fiscal_year, 06, 30)
VAR _tomorrow_date = IF(_today_date + 1 > _fiscal_year_end, _fiscal_year_end, _today_date + 1)
VAR _calendar_year = YEAR(_today_date)
VAR _calendar_year_start = DATE( _calendar_year , 01, 01)
VAR _calendar_year_end = DATE( _calendar_year, 12, 31)
VAR _previous_month_start = IF(MONTH(_today_date) = 1, DATE(YEAR(_today_date)-1, 12, 1), DATE(YEAR(_today_date), MONTH(_today_date)-1, 1))
VAR _previous_month_end = DATE(YEAR(_previous_month_start), MONTH(_previous_month_start), DAY(EOMONTH(_previous_month_start, 0)))
VAR _previous_quarter_start = EDATE(_quarter_start, -3)
VAR _previous_quarter_end = EOMONTH(EDATE(_quarter_start, -1), 0)
VAR _previous_fiscal_year_start = DATE( _fiscal_year - 2, 07, 01)
VAR _previous_fiscal_year_end = DATE( _fiscal_year - 1, 06, 30)
VAR _previous_calendar_year_start = DATE( _calendar_year - 1, 01, 01)
VAR _previous_calendar_year_end = DATE( _calendar_year - 1, 12, 31)
VAR _today_date_py = DATE( YEAR(_today_date) - 1, MONTH(_today_date), DAY(_today_date) )
VAR _week_start_py = DATE( YEAR(_today_date_py), 1 , 1) + (WEEKNUM(_today_date_py) - 1 ) * 7
VAR _month_start_py = DATE( YEAR(_today_date_py), MONTH(_today_date), 01 )
VAR _quarter_start_py = DATE( YEAR(_quarter_start) - 1, MONTH(_quarter_start), 01 )
VAR _fiscal_year_start_py = DATE( YEAR(_fiscal_year_start) - 1, MONTH(_fiscal_year_start), 01 )
VAR _result =
UNION (
ADDCOLUMNS (CALENDAR ( _today_date, _today_date), "Timeframe", "Today", "Timeframe Order", 1 )
, ADDCOLUMNS (CALENDAR ( _yesterday_date, _yesterday_date), "Timeframe", "Yesterday", "Timeframe Order", 2 )
// Week
, ADDCOLUMNS (CALENDAR ( _week_start - 7, _week_end - 7 ), "Timeframe", "Previous Week", "Timeframe Order", 3 )
, ADDCOLUMNS (CALENDAR ( _week_start, _week_end ), "Timeframe", "Current Week", "Timeframe Order", 4 )
, ADDCOLUMNS (CALENDAR ( _week_start, _today_date ), "Timeframe", "WTD", "Timeframe Order", 5 )
, ADDCOLUMNS (CALENDAR ( _week_start_py, _today_date_py ), "Timeframe", "WTD Previous Year", "Timeframe Order", 6 )
// Month
, ADDCOLUMNS (CALENDAR ( _previous_month_start, _previous_month_end ), "Timeframe", "Previous Month", "Timeframe Order", 7 )
, ADDCOLUMNS (CALENDAR ( _month_start, _month_end ), "Timeframe", "Current Month", "Timeframe Order", 8 )
, ADDCOLUMNS (CALENDAR ( _month_start, _today_date ), "Timeframe", "MTD", "Timeframe Order", 9 )
, ADDCOLUMNS (CALENDAR ( _month_start_py, _today_date_py ), "Timeframe", "MTD Previous Year", "Timeframe Order", 10 )
// Quarter
, ADDCOLUMNS (CALENDAR ( _previous_quarter_start, _previous_quarter_end ), "Timeframe", "Previous Qtr", "Timeframe Order", 11 )
, ADDCOLUMNS (CALENDAR ( _quarter_start, _quarter_end ), "Timeframe", "Current Qtr", "Timeframe Order", 12 )
, ADDCOLUMNS (CALENDAR ( _quarter_start, _today_date ), "Timeframe", "QTD", "Timeframe Order", 13 )
, ADDCOLUMNS (CALENDAR ( _quarter_start_py, _today_date_py ), "Timeframe", "QTD Previous Year", "Timeframe Order", 14 )
// Financial Year
, ADDCOLUMNS (CALENDAR ( _previous_fiscal_year_start, _previous_fiscal_year_end ), "Timeframe", "Previous Fiscal Year", "Timeframe Order", 15 )
, ADDCOLUMNS (CALENDAR ( _fiscal_year_start_py, _today_date_py ), "Timeframe", "YTD Previous Fiscal Year", "Timeframe Order", 16 )
, ADDCOLUMNS (CALENDAR ( _fiscal_year_start, _fiscal_year_end ), "Timeframe", "Current Fiscal Year", "Timeframe Order", 17 )
, ADDCOLUMNS (CALENDAR ( _fiscal_year_start, _today_date ), "Timeframe", "YTD Fiscal", "Timeframe Order", 18 )
, ADDCOLUMNS (CALENDAR ( _tomorrow_date, _fiscal_year_end ), "Timeframe", "Rest of Fiscal Year", "Timeframe Order", 19 )
// Calendar Year
, ADDCOLUMNS (CALENDAR ( _previous_calendar_year_start, _previous_calendar_year_end ), "Timeframe", "Previous Calendar Year", "Timeframe Order", 20 )
, ADDCOLUMNS (CALENDAR ( _previous_calendar_year_start, _today_date_py ), "Timeframe", "YTD Previous Calendar Year", "Timeframe Order", 21 )
, ADDCOLUMNS (CALENDAR ( _calendar_year_start, _calendar_year_end ), "Timeframe", "Current Calendar Year", "Timeframe Order", 22 )
, ADDCOLUMNS (CALENDAR ( _calendar_year_start, _today_date ), "Timeframe", "YTD Calendar", "Timeframe Order", 23 )
, ADDCOLUMNS (CALENDAR ( _tomorrow_date, _calendar_year_end ), "Timeframe", "Rest of Calendar Year", "Timeframe Order", 24 )
)
RETURN
_result
