求DAX公式计算客户连续合作年数（断档后计数重置）

需求：编写DAX公式计算客户与我方的连续合作年数，同一客户（以Transformed Register number标识）每年可能存在多条记录，当年份出现间隔时，连续计数需重置。

现有公式（计算结果不符合预期）：

Consecutive Yrs = 
VAR vMaxYear =
    MAX ( 'Data: Policy'[Effective year] )
VAR vThisCustomer =
    MIN ( 'Data: Policy'[Transformed Register number])
VAR vLatestNotDonation =
    MAXX (
        FILTER (
            ALL ( 'Data: Policy'[Effective year]),
            ISBLANK (
                CALCULATE (
                    COUNTROWS ( 'Data: Policy' ),
                    'Data: Policy'[Transformed Register number] = vThisCustomer
                )
            )
        ),
        'Data: Policy'[Effective year]
    )
VAR vLatestIfBlank =
    IF (
        ISBLANK ( vLatestNotDonation ),
        MINX (
            ALL ( 'Data: Policy'[Effective year] ),
            'Data: Policy'[Effective year]
        ) - 1,
        vLatestNotDonation
    )
RETURN
    IF (
        vMaxYear = 2023,
        vMaxYear - vLatestIfBlank
    )

示例数据与结果对比 #

修正后的DAX公式 #

Consecutive Yrs = 
VAR CurrentCustomer = SELECTEDVALUE('Data: Policy'[Transformed Register number])
VAR CurrentYear = SELECTEDVALUE('Data: Policy'[Effective year])
-- 获取当前客户所有有记录的唯一年份
VAR CustomerYears = 
    CALCULATETABLE(
        DISTINCT('Data: Policy'[Effective year]),
        'Data: Policy'[Transformed Register number] = CurrentCustomer
    )
-- 为每个年份匹配最近的中断点（当前年份前无记录的最大年份）
VAR YearGroups = 
    ADDCOLUMNS(
        CustomerYears,
        "LastGapYear",
        MAXX(
            FILTER(
                ALL('Data: Policy'[Effective year]),
                'Data: Policy'[Effective year] < [Effective year]
                && NOT('Data: Policy'[Effective year] IN CustomerYears)
            ),
            'Data: Policy'[Effective year]
        )
    )
-- 处理无中断点的情况，默认用客户最早合作年份-1作为起始
VAR YearGroupsWithDefault = 
    ADDCOLUMNS(
        YearGroups,
        "GroupStart",
        IF(ISBLANK([LastGapYear]), MINX(CustomerYears, [Effective year]) - 1, [LastGapYear])
    )
-- 计算当前年份对应的连续年数
VAR ConsecutiveCount = 
    CurrentYear - MAXX(FILTER(YearGroupsWithDefault, [Effective year] = CurrentYear), [GroupStart])
RETURN
    ConsecutiveCount

公式说明 #

1. 先提取当前客户所有有合作记录的唯一年份，规避同一年多条记录的干扰；
2. 针对每个年份，找到最近的"中断年份"（该年份之前客户无合作记录的最大年份），以此作为连续计数的起始点；
3. 用当前年份减去起始点得到连续年数，自动处理间隔重置逻辑；
4. 兼容单一年份、多年连续、中间有间隔等多种场景。

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