Date Selection =
VAR __FY = 07
VAR __TodaysDate = TODAY()
VAR __CYearStart = CALCULATE(STARTOFYEAR('Date'[Date]),YEAR('Date'[Date]) = YEAR(__TodaysDate))
VAR __CYearEnd = CALCULATE(ENDOFYEAR('Date'[Date]),YEAR('Date'[Date]) = YEAR(__TodaysDate))
VAR __FYearStart = IF(DATE(YEAR(TODAY()), VALUE(__FY), 1) <= TODAY(), DATE(YEAR(TODAY()), VALUE(__FY), 1), DATE(YEAR(TODAY()) - 1, VALUE(__FY), 1))
VAR __MonthStart = CALCULATE(STARTOFMONTH('Date'[Date]),YEAR('Date'[Date]) = YEAR(__TodaysDate),MONTH('Date'[Date]) = MONTH(__TodaysDate))
VAR __MonthEnd = CALCULATE(ENDOFMONTH('Date'[Date]),YEAR('Date'[Date]) = YEAR(__TodaysDate),MONTH('Date'[Date]) = MONTH(__TodaysDate))
--Last Year
VAR __SameDayLY = DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY()))
VAR __MonthStartLY = DATE(YEAR(__MonthStart) - 1, MONTH(__MonthStart), DAY(__MonthStart))
VAR __MonthEndLY = DATE(YEAR(__MonthEnd) - 1, MONTH(__MonthEnd), DAY(__MonthEnd))
VAR __QuarterStartLY = CALCULATE(STARTOFQUARTER('Date'[Date]),YEAR('Date'[Date]) = YEAR(__TodaysDate) - 1, quarter('Date'[Date]) = quarter(__TodaysDate))
VAR __QuarterEndLY = CALCULATE(ENDOFQUARTER('Date'[Date]),YEAR('Date'[Date]) = YEAR(__TodaysDate) - 1, quarter('Date'[Date]) = quarter(__TodaysDate))
VAR __LYearStart = CALCULATE(STARTOFYEAR('Date'[Date]),YEAR('Date'[Date]) = YEAR(__TodaysDate) - 1)
VAR __LYearEnd = CALCULATE(ENDOFYEAR('Date'[Date]),YEAR('Date'[Date]) = YEAR(__TodaysDate) - 1)
----------------------------
VAR __Last1Month = CALCULATE(
STARTOFMONTH('Date'[Date]),
FILTER('Date', 'Date'[Date] >= EDATE(TODAY(),-1))
)
VAR __Last6Months = CALCULATE(
STARTOFMONTH('Date'[Date]),
FILTER('Date', 'Date'[Date] >= EDATE(TODAY(),-6))
)
VAR __Last12Months = CALCULATE(
STARTOFMONTH('Date'[Date]),
FILTER('Date', 'Date'[Date] >= EDATE(TODAY(),-12) )
)
VAR __Last24Months = CALCULATE(
STARTOFMONTH('Date'[Date]),
FILTER('Date', 'Date'[Date] >= EDATE(TODAY(),-24) )
)
var __EarliestDate = MIN('Date'[Date])
VAR __QuarterStart = CALCULATE(STARTOFQUARTER('Date'[Date]),YEAR('Date'[Date]) = YEAR(__TodaysDate),quarter('Date'[Date]) = quarter(__TodaysDate))
VAR __QuarterEnd= CALCULATE(ENDOFQUARTER('Date'[Date]),YEAR('Date'[Date]) = YEAR(__TodaysDate),quarter('Date'[Date]) = quarter(__TodaysDate))
VAR __LastQuarterStart =
CALCULATE(
STARTOFQUARTER('Date'[Date]),
YEAR('Date'[Date]) = YEAR(TODAY()) - IF(QUARTER(TODAY()) = 1, 1, 0),
QUARTER('Date'[Date]) = IF(QUARTER(TODAY()) = 1, 4, QUARTER(TODAY()) - 1)
)
VAR __LastQuarterEnd =
CALCULATE(
ENDOFQUARTER('Date'[Date]),
YEAR('Date'[Date]) = YEAR(TODAY()) - IF(QUARTER(TODAY()) = 1, 1, 0),
QUARTER('Date'[Date]) = IF(QUARTER(TODAY()) = 1, 4, QUARTER(TODAY()) - 1)
)
VAR __LastMonthStart =
CALCULATE(
STARTOFMONTH('Date'[Date]),
YEAR('Date'[Date]) = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0),
MONTH('Date'[Date]) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1)
)
VAR __LastMonthEnd =
CALCULATE(
ENDOFMONTH('Date'[Date]),
YEAR('Date'[Date]) = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0),
MONTH('Date'[Date]) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1)
)
VAR RESULT =
UNION(
ADDCOLUMNS(CALENDAR(__EarliestDate,__TodaysDate),"Selection","All","Order",1),
ADDCOLUMNS(CALENDAR(__FYearStart,__TodaysDate),"Selection","Financial Year to Date","Order",3),
ADDCOLUMNS(CALENDAR(__MONTHSTART,__TodaysDate),"Selection","Month to Date","Order",4),
ADDCOLUMNS(CALENDAR(__CYearStart,__TodaysDate),"Selection","Year to Date","Order",5),
ADDCOLUMNS(CALENDAR(__TodaysDate - 365,__TodaysDate),"Selection","Last 12 Months","Order",7),
ADDCOLUMNS(CALENDAR(__TodaysDate - 730,__TodaysDate),"Selection","Last 24 Months","Order",8),
ADDCOLUMNS(CALENDAR(__TodaysDate - 30,__TodaysDate),"Selection","Last 30 days","Order",9),
ADDCOLUMNS(CALENDAR(__TodaysDate - 60,__TodaysDate),"Selection","Last 60 days","Order",10),
ADDCOLUMNS(CALENDAR(__TodaysDate - 90,__TodaysDate),"Selection","Last 90 days","Order",11),
ADDCOLUMNS(CALENDAR(__MONTHSTART,__MonthEnd),"Selection","Current Month","Order",13),
ADDCOLUMNS(CALENDAR(__CYearStart,__CYearEnd),"Selection","Current Year","Order",14),
ADDCOLUMNS(CALENDAR(__LastMonthStart,__LastMonthEnd),"Selection","Previous Month","Order",15),
ADDCOLUMNS(CALENDAR(__LYearStart,__LYearEnd),"Selection","Previous Year","Order",16),
ADDCOLUMNS(CALENDAR(__TodaysDate,__TodaysDate),"Selection","Today","Order",17),
ADDCOLUMNS(CALENDAR(__TodaysDate - 1,__TodaysDate - 1),"Selection","Yesterday","Order",18),
ADDCOLUMNS(CALENDAR(__TodaysDate - 7,__TodaysDate),"Selection","Last Week","Order",19)
)
RETURN RESULT