使用DAX计算含订阅起止日期的MRR月度总额及累计值

前置准备 #

你需要先准备一张独立的无关联日期维度表，不需要和订阅表建立物理关系，用于提供月度统计的上下文维度。你可以直接用DAX新建计算表生成：

日期表 = 
ADDCOLUMNS(
    CALENDAR(DATE(2021,1,1), DATE(2021,12,31)), // 可按需调整统计的日期范围
    "年月", FORMAT([日期], "YYYY-MM"),
    "年份", YEAR([日期]),
    "月份", MONTH([日期])
)

生成日期表后，右键选中该表，选择「标记为日期表」，保证后续计算逻辑正确。

核心度量值编写 #

1. 月度MRR总额度量值 #

月度MRR总额 = 
VAR 当前统计月首日 = MIN('日期表'[日期])
VAR 当前统计月末日 = EOMONTH(当前统计月首日, 0)
RETURN
CALCULATE(
    SUM('订阅表'[月度金额]),
    FILTER(
        ALL('订阅表'),
        // 订阅开始时间早等于当月最后一天
        '订阅表'[订阅开始日期] <= 当前统计月末日
        &&
        // 激活状态结束日期为空默认符合条件，已取消的判断结束日期覆盖当月
        IF(
            ISBLANK('订阅表'[结束日期]),
            TRUE(),
            '订阅表'[结束日期] >= 当前统计月首日
        )
    )
)

该度量值会自动判断当前统计的月份范围，仅将处于激活周期内的订阅金额计入当月，自动处理激活用户结束日期为空的情况，输出结果和你给出的预期数值完全匹配。

2. 运行累计MRR度量值 #

累计MRR = 
VAR 当前统计年月 = MAX('日期表'[年月])
RETURN
CALCULATE(
    [月度MRR总额],
    FILTER(
        ALL('日期表'[年月]),
        '日期表'[年月] <= 当前统计年月
    )
)

使用方式 #

将日期表的「年月」字段放到透视表/矩阵的行维度，依次拖入上述两个度量值即可输出你需要的月度MRR及累计值结果。

规则调整说明 #

如果后续需要修改订阅结束当月是否计入的规则，只需要调整月度MRR总额度量值中结束日期的判断条件即可：

• 结束当月计入：保持现有'订阅表'[结束日期] >= 当前统计月首日的写法
• 结束当月不计入：修改为'订阅表'[结束日期] > 当前统计月末日

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