PowerBI：基于列匹配完整交易并计算Number差值

原始数据集 #

需求目标 #

按用户匹配每笔完整交易（即Open状态记录后紧跟的首个Close状态记录），计算对应Number字段的差值，生成如下结果表：

实现方案 #

方法一：Power Query 数据预处理（推荐） #

通过Power Query完成数据转换，适合提前整理好数据集：

1. 导入数据到Power Query
   打开PowerBI，将数据集导入Power Query编辑器。

2. 添加用户内排序与索引
   按用户分组后，对每组记录按Date排序，添加组内索引标记记录顺序：

   let
       源 = 你的数据源,
       按用户分组 = Table.Group(源, {"User"}, {{"组内数据", each _, type table [TransactionState=text, Date=date, Number=number, User=text]}}),
       添加排序和索引 = Table.TransformColumns(按用户分组, {"组内数据", each Table.AddIndexColumn(Table.Sort(_, {"Date", Order.Ascending}), "组内索引", 0, 1)}),
       展开组内数据 = Table.ExpandTableColumn(添加排序和索引, "组内数据", {"TransactionState", "Date", "Number", "组内索引"}, {"TransactionState", "Date", "Number", "组内索引"})
   in
       展开组内数据
   

3. 匹配Open对应的首个Close记录
   筛选出Open状态的记录，为每条Open记录匹配后续首个Close记录并计算差值：

   let
       筛选Open记录 = Table.SelectRows(展开组内数据, each [TransactionState] = "Open"),
       匹配Close记录 = Table.AddColumn(筛选Open记录, "对应Close", (current) => 
           Table.First(Table.SelectRows(展开组内数据, 
               each [User] = current[User] and [TransactionState] = "Close" and [组内索引] > current[组内索引]
           ))),
       展开Close数据 = Table.ExpandRecordColumn(匹配Close记录, "对应Close", {"Date", "Number"}, {"ClosedOnDate", "CloseNumber"}),
       计算Used = Table.AddColumn(展开Close数据, "Used", each [CloseNumber] - [Number]),
       整理列 = Table.SelectColumns(计算Used, {"Date", "ClosedOnDate", "Used", "User"}),
       重命名列 = Table.RenameColumns(整理列, {{"Date", "OpenedOnDate"}})
   in
       重命名列
   

4. 加载数据回PowerBI
   完成转换后，关闭Power Query编辑器，将处理好的数据集加载到PowerBI模型中。

方法二：DAX 计算表（动态计算） #

如果需要在模型中动态生成结果，可创建DAX计算表：

完整交易表 = 
VAR 带用户内索引的交易 = 
    ADDCOLUMNS(
        交易表,
        "用户内索引", RANKX(FILTER(交易表, 交易表[User] = EARLIER(交易表[User])), 交易表[Date],, ASC, DENSE)
    )
VAR Open记录集 = FILTER(带用户内索引的交易, 交易表[TransactionState] = "Open")
VAR 匹配Close记录 = 
    ADDCOLUMNS(
        Open记录集,
        "ClosedOnDate", 
            MAXX(
                FILTER(带用户内索引的交易, 
                    带用户内索引的交易[User] = [User] && 
                    带用户内索引的交易[TransactionState] = "Close" && 
                    带用户内索引的交易[用户内索引] > [用户内索引]
                ),
                带用户内索引的交易[Date]
            ),
        "CloseNumber",
            MAXX(
                FILTER(带用户内索引的交易, 
                    带用户内索引的交易[User] = [User] && 
                    带用户内索引的交易[TransactionState] = "Close" && 
                    带用户内索引的交易[用户内索引] > [用户内索引]
                ),
                带用户内索引的交易[Number]
            )
    )
RETURN
    SELECTCOLUMNS(
        匹配Close记录,
        "OpenedOnDate", [Date],
        "ClosedOnDate", [ClosedOnDate],
        "Used", [CloseNumber] - [Number],
        "User", [User]
    )

注意事项 #

• 如果记录包含时间戳，排序时需将Date替换为包含时间的字段，确保顺序准确。
• 若存在未匹配的Open记录（无对应Close），可在筛选步骤中添加条件剔除这些无效记录。

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