如何用DAX在Power Pivot中计算流失用户与回流用户？

问题背景 #

我在Excel 365的Power Pivot中有以下几张表：

• 日常投注额报表：包含日期、用户名、投注金额；
• 用户列表：包含唯一用户名及用户注册日期；
• 代理列表：包含用户名及上级（唯一用户）；
• Power Pivot生成的日历表：已关联日常投注额报表的日期字段。

需求 #

需要计算每月流失用户数并在透视报表中展示，流失用户定义为超过30天未投注的用户；同时计算回流用户数（即流失后再次投注的用户）。这些计算需响应透视报表中的日期及代理筛选，筛选条件变更时数据需同步更新。

原尝试的回流用户DAX公式 #

Returning Users :=
VAR CurrentDate = MAX ( 'Calendar'[Date] )
RETURN
CALCULATE (
    COUNT ( '新注册用户New user'[用户名称] ),
    FILTER (
        ALL ( '新注册用户New user'[用户名称] ),
        VAR LastBetDate = CALCULATE (
            MAX ( '三方日常投注额 日期 Third party betting  by date by website'[日期] ),
            ALL ( '三方日常投注额 日期 Third party betting  by date by website' )
        )
        VAR SecondLastBetDate = CALCULATE (
            MAX ( '三方日常投注额 日期 Third party betting  by date by website'[日期] ),
            ALL ( '三方日常投注额 日期 Third party betting  by date by website' ),
            '三方日常投注额 日期 Third party betting  by date by website'[日期] < LastBetDate
        )
        RETURN
        LastBetDate <= CurrentDate && LastBetDate > CurrentDate - 30
            && SecondLastBetDate <= CurrentDate - 30
    )
)

优化方案 #

1. 核心问题修正 #

原公式使用ALL('三方日常投注额...')会完全清除投注表的筛选上下文，导致代理筛选、日期筛选失效，这是核心问题。下面的优化会保留用户维度的筛选逻辑，确保筛选器生效。

2. 拆分度量值提升效率 #

先拆分出基础度量值，缓存用户的投注日期数据，避免重复计算：

步骤1：计算用户最后投注日期

用户最后投注日期 :=
CALCULATE(
    MAX('日常投注额报表'[日期]),
    ALLEXCEPT('日常投注额报表', '日常投注额报表'[用户名])
)

步骤2：计算用户倒数第二次投注日期

用户倒数第二次投注日期 :=
VAR _LastDate = [用户最后投注日期]
RETURN
CALCULATE(
    MAX('日常投注额报表'[日期]),
    ALLEXCEPT('日常投注额报表', '日常投注额报表'[用户名]),
    '日常投注额报表'[日期] < _LastDate
)

步骤3：优化后的回流用户数度量值

回流用户数 :=
VAR _CurrentPeriodEnd = MAX('Calendar'[Date])
VAR _30DaysAgo = _CurrentPeriodEnd - 30
RETURN
CALCULATE(
    DISTINCTCOUNT('用户列表'[用户名]),
    FILTER(
        VALUES('用户列表'[用户名]),
        // 最后投注在当前统计周期内
        [用户最后投注日期] <= _CurrentPeriodEnd && [用户最后投注日期] > _30DaysAgo
        // 倒数第二次投注早于30天前（证明曾经流失）
        && [用户倒数第二次投注日期] <= _30DaysAgo
    )
)

3. 月度流失用户数度量值实现 #

月度流失用户数 :=
VAR _CurrentMonthEnd = MAX('Calendar'[Date])
VAR _30DaysAgo = _CurrentMonthEnd - 30
// 近30天有投注的活跃用户
VAR _ActiveUsers = 
    CALCULATETABLE(
        VALUES('日常投注额报表'[用户名]),
        '日常投注额报表'[日期] > _30DaysAgo && '日常投注额报表'[日期] <= _CurrentMonthEnd
    )
// 所有存在的用户
VAR _AllUsers = VALUES('用户列表'[用户名])
RETURN
COUNTROWS(EXCEPT(_AllUsers, _ActiveUsers))

优化亮点 #

• 用ALLEXCEPT替代ALL，保留用户维度筛选，确保代理、日期筛选生效；
• 拆分度量值后逻辑更清晰，同时利用DAX缓存机制提升计算速度；
• 用DISTINCTCOUNT替代COUNT，避免重复统计同一用户；
• 流失用户用集合运算EXCEPT实现，逻辑简洁高效。

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