PowerBI中VLOOKUP用法及基于合同编号日期对比判断新签/终止合同的DAX方案咨询

嘿，我来帮你搞定这个合同状态判断的需求！首先得先明确你的表结构——看起来应该是每日合同快照表（每天记录当天所有有效的合同编号），对吧？你想要实现的效果类似Excel里的VLOOKUP，对比当天和前一天的合同编号，标记哪些是新签、哪些是终止，下面给你一步步讲DAX的实现方法：

一、逐行标记"新签合同"（计算列） #

如果你的需求是给表中每一行合同编号标记它在当天是否是新签的，可以创建一个计算列，用DAX模拟VLOOKUP的逻辑去查找前一天是否存在该合同：

合同状态 = 
VAR 当前快照日期 = '你的表名'[DATE]
VAR 当前合同编号 = '你的表名'[CC]
VAR 前一天日期 = 当前快照日期 - 1
// 用CALCULATE+FILTER模拟VLOOKUP，查找前一天是否有这个合同
VAR 前一天是否存在该合同 = 
    CALCULATE(
        COUNTROWS('你的表名'),
        FILTER(
            ALL('你的表名'), // 要排除当前行的上下文，所以用ALL
            '你的表名'[DATE] = 前一天日期 && '你的表名'[CC] = 当前合同编号
        )
    ) > 0
RETURN
    IF(
        ISBLANK(前一天日期), // 处理日期为空的情况
        "未知",
        IF(
            前一天是否存在该合同,
            "存续", // 前一天已有，说明不是新签
            "NEW CONTRACT" // 前一天没有，标记为新签
        )
    )

如果你更习惯用类似Excel VLOOKUP的函数，也可以用LOOKUPVALUE来实现：

合同状态 = 
VAR 当前快照日期 = '你的表名'[DATE]
VAR 当前合同编号 = '你的表名'[CC]
VAR 前一天日期 = 当前快照日期 - 1
VAR 前一天匹配的合同 = 
    LOOKUPVALUE(
        '你的表名'[CC],
        '你的表名'[DATE], 前一天日期,
        '你的表名'[CC], 当前合同编号
    )
RETURN
    IF(
        ISBLANK(前一天匹配的合同),
        "NEW CONTRACT",
        "存续"
    )

二、统计"终止合同"（度量值） #

要注意：终止的合同在当天的快照表中是没有记录的，所以没法直接在计算列里标记。我们可以创建一个度量值，用来统计每天终止的合同数量：

当日终止合同数 = 
VAR 当前日期 = MAX('你的表名'[DATE])
VAR 前一天日期 = 当前日期 - 1
// 获取前一天所有的合同编号集合
VAR 前一天合同集合 = CALCULATE(VALUES('你的表名'[CC]), '你的表名'[DATE] = 前一天日期)
// 获取当天所有的合同编号集合
VAR 当日合同集合 = VALUES('你的表名'[CC])
// 找出前一天有但当天没有的合同，就是终止的
RETURN
    COUNTROWS(EXCEPT(前一天合同集合, 当日合同集合))

同理，也可以创建一个统计当日新签合同数的度量值：

当日新签合同数 = 
VAR 当前日期 = MAX('你的表名'[DATE])
VAR 前一天日期 = 当前日期 - 1
VAR 前一天合同集合 = CALCULATE(VALUES('你的表名'[CC]), '你的表名'[DATE] = 前一天日期)
VAR 当日合同集合 = VALUES('你的表名'[CC])
RETURN
    COUNTROWS(EXCEPT(当日合同集合, 前一天合同集合))

三、注意事项 #

1. 确保你的DATE列是日期格式，且没有无效值；
2. 如果你的日期不是连续的（比如周末没有快照），可以把前一天日期 = 当前快照日期 - 1改成前一天日期 = CALCULATE(MAX('你的表名'[DATE]), '你的表名'[DATE] < 当前快照日期)，这样会自动取最近的上一个快照日期；
3. 如果你的表不是每日快照，而是合同的变动记录（比如每行是合同的生效/终止日期），可以告诉我具体结构，我再调整DAX公式~

备注：内容来源于stack exchange，提问作者Deforceh