如何在DAX中计算所选年份的会员留存率？求正确DAX公式

我有一张包含Start_Date和End_Date字段的Membership表，已创建Date表并将其Date字段与Membership表的Start_Date建立关联，且设置了日期切片器。需要计算所选年份的会员留存率，尝试了以下两个DAX查询但未返回任何值，同时不确定2022年留存率的公式是否正确，请求提供正确的DAX查询。

尝试的DAX公式1：

RR = 
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
RETURN
CALCULATE(
DIVIDE(
    COUNTROWS(
        FILTER(
            Membership,
            Membership[End_Date] >= DATE(SelectedYear, 1, 1) 
            && Membership[End_Date] < DATE(SelectedYear + 1, 1, 1)
        )
    ),
    COUNTROWS(
        FILTER(
            Membership,
            Membership[Start_Date] < DATE(SelectedYear, 1, 1)
            && (
                Membership[End_Date] >= DATE(SelectedYear, 1, 1) 
                || ISBLANK(Membership[End_Date])
            )
        )
    )
), USERELATIONSHIP('Date'[Date],MEMBERSHIP[START_DATE]))

尝试的DAX公式2：

Retention Rate 2022 = 
DIVIDE(
    COUNTROWS(
        FILTER(
            'MEMBERSHIP',
            'MEMBERSHIP'[END_DATE]>= DATE(2022,1,1) 
            && 'MEMBERSHIP'[END_DATE] < DATE(2023,1,1)
        )
    ),
    COUNTROWS(
        FILTER(
            'MEMBERSHIP',
            'MEMBERSHIP'[START_DATE]< DATE(2022,1,1)
            && (
                'MEMBERSHIP'[END_DATE] >= DATE(2022,1,1) 
                || ISBLANK('MEMBERSHIP'[END_DATE])
            )
        )
    )
)

原公式问题分析 #

1. 筛选上下文冲突：公式1中使用USERELATIONSHIP但切片器基于Date表与Start_Date的关联，导致筛选上下文仅包含Start_Date在切片器年份的会员，无法覆盖所有在选中年份前开始的会员，因此返回空值。
2. 计数逻辑错误：使用COUNTROWS可能重复计数（若单一会员有多条记录），且分子统计的是当年到期的会员数，而非留存的活跃会员数，逻辑与留存率定义相悖。
3. 未处理除数为0的情况：当分母为0时，DIVIDE若无默认值会返回空值。

正确DAX公式 #

动态年份留存率（适配日期切片器）

会员留存率 = 
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
VAR YearStart = DATE(SelectedYear, 1, 1)
VAR YearEnd = DATE(SelectedYear, 12, 31)
-- 年初活跃会员数：当年1月1日前已开通会员，且在1月1日仍未过期（或未设置结束日期）
VAR StartOfYearActive = 
    CALCULATE(
        DISTINCTCOUNT(Membership[会员ID]), -- 替换为你的会员唯一标识字段
        Membership[Start_Date] <= YearStart,
        OR(Membership[End_Date] >= YearStart, ISBLANK(Membership[End_Date])),
        ALL('Date') -- 清除切片器对会员表的筛选限制
    )
-- 年末留存会员数：年初活跃会员中，到当年12月31日仍未过期（或未设置结束日期）
VAR EndOfYearRetained = 
    CALCULATE(
        DISTINCTCOUNT(Membership[会员ID]),
        Membership[Start_Date] <= YearStart,
        OR(Membership[End_Date] >= YearEnd, ISBLANK(Membership[End_Date])),
        ALL('Date')
    )
RETURN
DIVIDE(EndOfYearRetained, StartOfYearActive, 0) -- 除数为0时返回0

固定2022年留存率

2022年会员留存率 = 
VAR YearStart = DATE(2022, 1, 1)
VAR YearEnd = DATE(2022, 12, 31)
VAR StartOfYearActive = 
    CALCULATE(
        DISTINCTCOUNT(Membership[会员ID]),
        Membership[Start_Date] <= YearStart,
        OR(Membership[End_Date] >= YearStart, ISBLANK(Membership[End_Date]))
    )
VAR EndOfYearRetained = 
    CALCULATE(
        DISTINCTCOUNT(Membership[会员ID]),
        Membership[Start_Date] <= YearStart,
        OR(Membership[End_Date] >= YearEnd, ISBLANK(Membership[End_Date]))
    )
RETURN
DIVIDE(EndOfYearRetained, StartOfYearActive, 0)

说明 #

• 替换公式中的会员ID为你表中实际的会员唯一标识字段，避免重复计数。
• 留存率定义为：年末仍活跃的会员数 / 年初活跃的会员数，符合通用的会员留存统计逻辑。

内容的提问来源于stack exchange，提问作者Anil Kumar Poda