XF.Calendar.DateTable

语法作用

根据指定的起始年份与结束年份，生成一张能覆盖所有日期的标准日期表；StartDayOfWeek参数用于指定每周的第一天，1:周日,2:周一。

XF.Calendar.DateTable(startYear:int64,endYear:int64,startDayOfWeek:anyref)

函数代码

该函数的实现代码如下：

/// 根据指定的起始年份与结束年份，生成一张能覆盖所有日期的标准日期表；StartDayOfWeek参数用于指定每周的第一天，1:周日,2:周一
FUNCTION XF.Calendar.DateTable = (startYear:int64,endYear:int64,startDayOfWeek:anyref) =>
    ADDCOLUMNS(
        CALENDAR(DATE(startYear,1,1),DATE(endYear,12,31)),
        "Year Number",YEAR([Date]),
        "Year",FORMAT([Date],"yyyy"),
        "Month Number",MONTH([Date]),
        "Month",FORMAT([Date],"mmmm"),
        "Quarter Number",ROUNDUP(MONTH([Date])/3,0),
        "Quarter","Q"&ROUNDUP(MONTH([Date])/3,0),
        "WeekNum",WEEKNUM([Date],startDayOfWeek),
        "WeekDay Number",WEEKDAY([Date],startDayOfWeek),
        "WeekDay",
            VAR WeekDayNum = WEEKDAY([Date],startDayOfWeek)
            RETURN
            SWITCH(
                startDayOfWeek,
                1,
                    SWITCH(WeekDayNum,1,"周日",2,"周一",3,"周二",4,"周三" ,5,"周四",6,"周五" ,7,"周六"), 
                2,
                    SWITCH(WeekDayNum,1,"周一",2,"周二",3,"周三",4,"周四" ,5,"周五",6,"周六" ,7,"周日")
            ),
        "YearMonth Number",YEAR([Date])*100+MONTH([Date]),
        "YearMonth",FORMAT([Date],"yy-mmmm"),
        "YearQuarter Number",YEAR([Date])*10+ROUNDUP(MONTH([Date])/3,0),
        "YearQuarter",YEAR([Date])&"Q"&ROUNDUP(MONTH([Date])/3,0)
    )

应用示例

生成2024年至2025年的日期表。

XF.Calendar.DateTable(2024,2025,2)

结果如下图所示：