如何用Power BI的Intersect函数计算许可证续订数量？

原始数据 #

需求说明 #

需要计算许可证的续订数量：

• 续订机会：指指定周期内许可证到期的总数量，例如Q2（2-4月）的续订机会为50个许可证；
• 续订数量：对应每个账户在该周期内新启动的许可证数量，仅统计同时存在到期记录的账户，例如Q2的续订数量为45个（机会ID17属于账户2，该账户在Q2无到期记录，故不计入）。

实现思路 #

计划通过以下逻辑实现：

1. 按账户ID汇总指定季度内到期的许可证，生成到期表；
2. 按账户ID汇总指定季度内启动的许可证，生成启动表；
3. 匹配两个表的账户ID：
   • 账户ID同时存在于两张表：说明该账户有到期且完成续订，取启动表中的数量计入续订数量；
   • 账户ID仅存在于到期表：说明无续订，不计入；
   • 账户ID仅存在于启动表：属于新增销售，不属于续订，不计入。

尝试的方法及问题 #

• 方法1：将包含账户ID和数量的到期表、启动表赋值给变量，但INTERSECT函数会同时匹配账户ID和数量列，无法仅按账户ID取交集；
• 方法2：创建仅含账户ID的到期表和启动表，虽然能通过INTERSECT获取共同账户ID，但无法关联回启动表获取对应数量。

现有DAX代码 #

Renewal via Tables = 
// 筛选周期内到期的许可证对应的账户ID
VAR lic_expire =
    CALCULATETABLE (
        FILTER (
            ALL ( Sheet1 ),
            Sheet1[Start] <= MAX ( fiscaldate[Date] )
                && Sheet1[End] >= MIN ( fiscaldate[Date] )
                && Sheet1[End] <= MAX ( fiscaldate[Date] )
        ),
        Sheet1[Account ID]
    )
// 筛选周期内启动的许可证对应的账户ID   
VAR lic_start =
    CALCULATETABLE (
        FILTER (
            ALL ( Sheet1 ),
            Sheet1[End] > MAX ( fiscaldate[Date] )
                && Sheet1[Start] >= MIN ( fiscaldate[Date] )
                && Sheet1[Start] <= MAX ( fiscaldate[Date] )
        ),
        Sheet1[Account ID]
    )

VAR commonIDs = INTERSECT(lic_expire, lic_start)

RETURN 
CALCULATE(
    SUMX (
        SUMMARIZE (
            CALCULATETABLE (
                FILTER (
                    ALL ( Sheet1 ),
                    Sheet1[End] > MAX ( fiscaldate[Date] )
                        && Sheet1[Start] >= MIN ( fiscaldate[Date] )
                        && Sheet1[Start] <= MAX ( fiscaldate[Date] )
                        && Sheet1[Account ID] IN {INTERSECT(lic_expire,lic_start)} // 此处报错，无法筛选共同账户ID以获取对应启动数量
                ),
                Sheet1[Account ID]
            ),
            "start qty", SUM ( Sheet1[Qty] )
        ),
        [start qty]
    )
)

问题点 #

代码中Sheet1[Account ID] IN {INTERSECT(lic_expire,lic_start)}部分报错，无法筛选出同时存在到期和启动记录的账户ID，进而无法统计对应启动数量。

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