如何用T-SQL或DAX计算日期表中每日的上一个意大利工作日

场景说明 #

现有日期表（假设表名为DateTable）结构及数据如下：

TheDate     ItalyWorkingDay
2039-12-28  1
2039-12-27  1
2039-12-26  0
2039-12-25  0
2039-12-24  0
2039-12-23  1
2039-12-22  1
2039-12-21  1

需要为每个日期计算上一个意大利工作日，期望输出结果：

TheDate     ItalyWorkingDay  PrevItWorkDay
2039-12-28  1               2039-12-27
2039-12-27  1               2039-12-23
2039-12-26  0               2039-12-23
2039-12-25  0               2039-12-23
2039-12-24  0               2039-12-23
2039-12-23  1               2039-12-22
2039-12-22  1               2039-12-21
2039-12-21  1               2039-12-20

T-SQL 解决方案 #

方法：使用OUTER APPLY关联子查询 #

通过OUTER APPLY为每个日期筛选出早于当前日期的最近工作日，子查询按日期倒序取第一条即可得到目标值：

SELECT 
    d.TheDate,
    d.ItalyWorkingDay,
    prev.TheDate AS PrevItWorkDay
FROM DateTable d
OUTER APPLY (
    SELECT TOP 1 TheDate
    FROM DateTable
    WHERE TheDate < d.TheDate
      AND ItalyWorkingDay = 1
    ORDER BY TheDate DESC
) prev
ORDER BY d.TheDate DESC;

说明 #

• OUTER APPLY确保即使没有更早的工作日（如日期表中最早的记录），结果仍会返回（此时PrevItWorkDay为NULL）
• 子查询中的ORDER BY TheDate DESC保证取到的是最近的上一个工作日

DAX 解决方案 #

方法1：CALCULATE + MAX 组合（计算列） #

在Power BI或Tabular模型中创建计算列，通过筛选当前日期之前的所有工作日，取最大日期即为最近的上一个工作日：

PrevItWorkDay = 
CALCULATE(
    MAX('DateTable'[TheDate]),
    FILTER(
        ALL('DateTable'),
        'DateTable'[TheDate] < EARLIER('DateTable'[TheDate])
            && 'DateTable'[ItalyWorkingDay] = 1
    )
)

方法2：TOPN + MAXX 组合（计算列） #

通过TOPN筛选出符合条件的最近一条记录，再提取日期值，逻辑更直观：

PrevItWorkDay = 
VAR CurrentDate = 'DateTable'[TheDate]
RETURN
MAXX(
    TOPN(
        1,
        FILTER('DateTable', 'DateTable'[TheDate] < CurrentDate && 'DateTable'[ItalyWorkingDay] = 1),
        'DateTable'[TheDate], DESC
    ),
    'DateTable'[TheDate]
)

说明 #

• EARLIER('DateTable'[TheDate])用于引用当前行的日期值
• 若日期表中没有更早的工作日，计算列会返回BLANK()，可根据需求用COALESCE处理默认值

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