Power BI中如何使用DAX正确计算滚动年度总计(MAT)

问题背景 #

需要实现12个月为周期的滚动诊疗量统计：

• 滚动规则：每个统计节点对应往前12个月的累计数据，例如标注为Jan 21 - Dec 21的周期，实际统计范围为2020/1/1至2020/12/1的全量数据，该区间内类别'A'累计出现4次
• 最终输出需支撑连续滚动的趋势可视化效果
• 相关参考截图：
  • 基础数据：
    
  • 预期计算结果样式：
    
  • 预期可视化效果：
    

原有代码问题 #

原有度量值运行结果不符合预期，代码如下：

Moving Annual Total = CALCULATE(
COUNTA('2017-2022Q1'[idnum]),
DATESINPERIOD('2017-2022Q1'[mat].[Date], LASTDATE('2017-2022Q1'[mat].[Date]), 12, MONTH))

核心错误有3点：

• 直接使用事实表的日期列作为时间智能函数的参数：DAX所有时间智能函数必须基于连续无缺失的独立日期维度表运算，直接用事实表日期会因为日期断层、重复值导致区间计算错误
• 滚动方向错误：DATESINPERIOD的偏移参数为正数时是向未来时间偏移，要统计过去12个月需要设置为-12
• 未处理周期标签和统计区间的偏移匹配：需求中展示的周期标签比实际统计区间晚12个月，原有逻辑没有做对应偏移，导致标签和数值对不上

正确实现步骤 #

1. 先创建独立的连续日期维度表，和事实表建立关联：

日期表 = 
VAR MinDate = MIN('2017-2022Q1'[mat])
VAR MaxDate = MAX('2017-2022Q1'[mat])
RETURN
ADDCOLUMNS(
    CALENDAR(MinDate, MaxDate),
    "年", YEAR([Date]),
    "月", MONTH([Date]),
    "MAT周期标签", FORMAT(EDATE([Date],12), "mmm yy") & " - " & FORMAT(EDATE([Date],23), "mmm yy")
)

创建完成后，将日期表的[Date]列和事实表'2017-2022Q1'[mat]字段建立一对多的单方向关系。

2. 编写正确的MAT度量值：

Moving Annual Total = 
CALCULATE(
    COUNTA('2017-2022Q1'[idnum]),
    DATESINPERIOD(
        '日期表'[Date],
        MAX('日期表'[Date]),
        -12,
        MONTH
    )
)

3. 制作可视化时，使用日期表的[MAT周期标签]作为轴字段，放入上述度量值即可得到和预期完全一致的结果。

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