如何使用T-SQL/DAX/M Query为表新增StartDate与EndDate列

以下分别提供三种工具的实现逻辑和可直接复用的代码：

T-SQL 实现 #

使用窗口函数LEAD直接提取同人员下一行的变更日期，执行效率最高：

SELECT 
    Date AS StartDate,
    Name,
    OldValue,
    NewValue,
    -- 无下一次变更时默认返回9999-12-31，可按需修改为NULL
    LEAD(Date,1,'9999-12-31') OVER (PARTITION BY Name ORDER BY Date ASC) AS EndDate
FROM 你的表名

DAX 实现 #

在数据模型中为表新建两个计算列即可：

1. StartDate 计算列：

StartDate = '你的表名'[Date]

2. EndDate 计算列：

EndDate = 
VAR CurrentDate = '你的表名'[Date]
VAR NextChangeDate = CALCULATE(
    MIN('你的表名'[Date]),
    ALLEXCEPT('你的表名','你的表名'[Name]),
    '你的表名'[Date] > CurrentDate
)
RETURN IF(ISBLANK(NextChangeDate), DATE(9999,12,31), NextChangeDate)

M Query（Power Query）实现 #

进入Power Query编辑器后，粘贴以下代码并替换源为你自己的数据源即可：

let
    // 替换下方的源为你自己的原始数据表
    源 = 你的原始数据表,
    // 按人员分组
    按人员分组 = Table.Group(源, {"Name"}, {{"分组数据", each _, type table [Date=date, Name=text, OldValue=any, NewValue=any]}}),
    // 处理单组内的日期逻辑
    每组添加日期字段 = Table.TransformColumns(按人员分组, {"分组数据", (groupTable) => 
        let
            // 按变更日期升序排序
            按日期排序 = Table.Sort(groupTable,{{"Date", Order.Ascending}}),
            // 原Date字段重命名为StartDate
            重命名日期字段 = Table.RenameColumns(按日期排序,{{"Date", "StartDate"}}),
            // 生成EndDate列表：移除第一个日期、末尾补最大日期对齐行数
            结束日期列表 = List.RemoveFirstN(重命名日期字段[StartDate], 1) & {#date(9999,12,31)},
            // 合并EndDate列到当前分组表
            合并EndDate = Table.FromColumns(Table.ToColumns(重命名日期字段) & {结束日期列表}, Table.ColumnNames(重命名日期字段) & {"EndDate"})
        in
            合并EndDate
    }}),
    // 展开分组得到最终结果
    展开结果 = Table.ExpandTableColumn(每组添加日期字段, "分组数据", {"StartDate", "OldValue", "NewValue", "EndDate"})
in
    展开结果

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