日历日期表WeekBegin列表达式咨询（周一为周起始）

针对你的Calendar Date表需求，以下是主流工具的实现代码，核心逻辑是先计算常规的周一起始日期，再判断其年份是否与原日期一致，若跨年度则将原日期作为周起始。

SQL 实现（分数据库） #

SQL Server #

SELECT
    Date,
    -- 计算周起始：若常规周一跨年则取原日期，否则取常规周一
    CASE
        WHEN YEAR(DATEADD(day, -(DATEPART(dw, Date) + 5) % 7, Date)) = YEAR(Date)
        THEN DATEADD(day, -(DATEPART(dw, Date) + 5) % 7, Date)
        ELSE Date
    END AS WeekBegin,
    -- 周结束为周起始加6天
    DATEADD(day, 6, 
        CASE
            WHEN YEAR(DATEADD(day, -(DATEPART(dw, Date) + 5) % 7, Date)) = YEAR(Date)
            THEN DATEADD(day, -(DATEPART(dw, Date) + 5) % 7, Date)
            ELSE Date
        END) AS WeekEnd,
    -- 周天数：跨年周为1天，正常周为7天
    CASE
        WHEN YEAR(DATEADD(day, -(DATEPART(dw, Date) + 5) % 7, Date)) = YEAR(Date)
        THEN 7
        ELSE 1
    END AS DaysinWeek
FROM CalendarDate;

逻辑说明：(DATEPART(dw, Date) + 5) % 7 兼容SQL Server默认周日为一周第一天的设置，计算出当前日期到上周一的偏移量；通过年份判断避免跨年，如2023-01-01（周日）的常规周一为2022-12-26，因跨年直接返回原日期。

MySQL #

SELECT
    `Date`,
    CASE
        WHEN YEAR(DATE_SUB(`Date`, INTERVAL WEEKDAY(`Date`) DAY)) = YEAR(`Date`)
        THEN DATE_SUB(`Date`, INTERVAL WEEKDAY(`Date`) DAY)
        ELSE `Date`
    END AS WeekBegin,
    DATE_ADD(
        CASE
            WHEN YEAR(DATE_SUB(`Date`, INTERVAL WEEKDAY(`Date`) DAY)) = YEAR(`Date`)
            THEN DATE_SUB(`Date`, INTERVAL WEEKDAY(`Date`) DAY)
            ELSE `Date`
        END, INTERVAL 6 DAY
    ) AS WeekEnd,
    CASE
        WHEN YEAR(DATE_SUB(`Date`, INTERVAL WEEKDAY(`Date`) DAY)) = YEAR(`Date`)
        THEN 7
        ELSE 1
    END AS DaysinWeek
FROM CalendarDate;

逻辑说明：MySQL的WEEKDAY()函数返回周一为0、周日为6，DATE_SUB减去对应天数得到常规周一；年份判断不满足时返回原日期。

Power Query（M语言） #

let
    Source = YourCalendarTable, // 替换为你的日历表数据源
    // 添加周起始列
    AddWeekBegin = Table.AddColumn(Source, "WeekBegin", each 
        let
            RegularWeekBegin = Date.AddDays([Date], -Date.DayOfWeek([Date], Day.Monday)),
            IsSameYear = Date.Year(RegularWeekBegin) = Date.Year([Date])
        in
            if IsSameYear then RegularWeekBegin else [Date]
    ),
    // 添加周结束列
    AddWeekEnd = Table.AddColumn(AddWeekBegin, "WeekEnd", each Date.AddDays([WeekBegin], 6)),
    // 添加周天数列
    AddDaysInWeek = Table.AddColumn(AddWeekEnd, "DaysinWeek", each if [WeekBegin] = [Date] then 1 else 7)
in
    AddDaysInWeek

逻辑说明：Date.DayOfWeek([Date], Day.Monday) 指定周一为一周起始，计算偏移量得到常规周一；通过年份判断处理跨年场景。

DAX（Power BI/Excel） #

// 周起始列
WeekBegin = 
VAR RegularMonday = [Date] - WEEKDAY([Date], 2) + 1 // WEEKDAY(,2) 表示周一为1
VAR SameYear = YEAR(RegularMonday) = YEAR([Date])
RETURN IF(SameYear, RegularMonday, [Date])

// 周结束列
WeekEnd = [WeekBegin] + 6

// 周天数列
DaysinWeek = IF([WeekBegin] = [Date], 1, 7)

逻辑说明：WEEKDAY([Date],2) 让周一返回1，计算得到常规周一；年份不匹配时返回原日期，确保周起始不跨年。

内容的提问来源于stack exchange，提问作者KMR