以下のYoutubeビデオで、カレンダーテーブルの作成について詳しく紹介されていましたので、カレンダーテーブルを作ったことがない方はぜひご覧ください。
また、この記事より @ishiayaya さんの記事の方が役に立ちます。
DateTable =
// 日付を指定するか、CALENDARAUTOに任せるか
// VAR BaseCalendar = CALENDAR( DATE(2016,1,1), DATE(2018,12,31) )
VAR BaseCalendar = CALENDARAUTO()
RETURN
GENERATE(
BaseCalendar,
VAR BaseDate = [Date]
VAR DayNumber = DAY( basedate )
VAR YearDate = YEAR( BaseDate )
VAR MonthNumber = MONTH( BaseDate )
VAR YearMonthName = FORMAT( BaseDate, "yyyymm" )
VAR YearMonthNumber = YearDate * 12 + MonthNumber - 1
VAR YearMonthDayName = FORMAT( BaseDate, "yyyymmdd" )
VAR DayOfWeekName = FORMAT( BaseDate, "aaa", "ja-JP" )
// // 月曜スタート
// VAR DayOfWeekNumber = WEEKDAY( BaseDate, 3 )
// VAR StartOfWeek = BaseDate - DayOfWeekNumber
// 日曜スタート
VAR DayOfWeekNumber = WEEKDAY( BaseDate, 1 )
VAR StartOfWeek = BaseDate - DayOfWeekNumber - 1
//
VAR YearWeekNumber = WEEKNUM( BaseDate )
VAR EndDate = EOMONTH( BaseDate, 0 )
VAR DaysOfMonth = DATEDIFF( BaseDate, EDATE( BaseDate , 1 ), DAY )
RETURN ROW (
// English
"Year", YearDate,
"Month", MonthNumber,
"Day", DayNumber,
"YearMonth_Number", YearMonthNumber,
"YearMonth", YearMonthName,
"YearMonthDay", YearMonthDayName,
"Week", DayOfWeekName,
"WeekNumber", DayOfWeekNumber,
"YearWeekNumber", YearWeekNumber,
"WeekStartDate", StartOfWeek,
"EndOfMonth", EndDate,
"Days of Month", DaysOfMonth
// // Japanese
// "年", YearDate,
// "月", MonthNumber,
// "日", DayNumber,
// "年月_番号", YearMonthNumber,
// "年月", YearMonthName,
// "年月日", YearMonthDayName,
// "曜日", DayOfWeekName,
// "曜日_番号", DayOfWeekNumber,
// "年曜日_番号", YearWeekNumber,
// "週開始日", StartOfWeek,
// "月末日", EndDate,
// "月日数", DaysOfMonth
)
)
