DAX实现字符串格式前3个期间金额求和问题排查

需求说明 #

数据结构 #

包含Year、Period、Account、Amount字段，示例数据如下：

核心需求 #

• 当透视表筛选2023年P01时，求和2022年P11、2022年P12、2023年P01的金额
• 当透视表筛选2023年P03时，求和2023年P01、2023年P02、2023年P03的金额

现有计算列问题分析 #

你写的计算列返回每行总计值，主要有3个原因：

1. 计算列的静态特性：计算列是逐行生成的静态值，不会响应透视表的筛选器变化，根本不适合实现这种动态求和需求，应该使用度量值。
2. 全局年份取值错误：MAX(Hyperion[Year])取的是整个数据表的最大年份，不是当前筛选器选中的年份，导致起始年份判断错误。
3. 未保留Account筛选：FILTER(Hyperion, ...)没有限制当前Account，计算的是所有Account的金额总和，而非当前行/筛选的Account金额。

正确实现方案：使用度量值 #

步骤1：基础金额总和度量值 #

先定义一个基础的金额求和度量值，方便后续复用：

Total Amount = SUM(Hyperion[Amount])

步骤2：前3期求和度量值 #

Last 3 Periods Amount = 
-- 获取当前筛选的年份和期间数字（将Pxx转换为数字）
VAR SelectedYear = SELECTEDVALUE(Hyperion[Year])
VAR SelectedPeriodNum = VALUE(RIGHT(SELECTEDVALUE(Hyperion[Period]), 2))

-- 计算起始期间的年份和数字
VAR StartPeriodNum = SelectedPeriodNum - 2
VAR StartYear = 
    IF(StartPeriodNum <= 0,
        SelectedYear - 1, -- 期间跨上一年时，起始年份减1
        SelectedYear
    )
VAR AdjustedStartPeriodNum = 
    IF(StartPeriodNum <= 0,
        12 + StartPeriodNum, -- 跨年时，计算上一年的对应期间（比如P01对应上一年P11）
        StartPeriodNum
    )

-- 生成所有符合条件的(Year, Period)组合
VAR ValidPeriods = 
    FILTER(
        ALL(Hyperion[Year], Hyperion[Period]),
        -- 上一年的起始期间到年末，或者当年的年初到选中期间
        (Hyperion[Year] = StartYear && VALUE(RIGHT(Hyperion[Period],2)) >= AdjustedStartPeriodNum)
        || (Hyperion[Year] = SelectedYear && VALUE(RIGHT(Hyperion[Period],2)) <= SelectedPeriodNum)
    )

-- 计算符合条件的金额总和，保留Account等其他筛选
RETURN
CALCULATE(
    [Total Amount],
    ValidPeriods,
    ALLSELECTED(Hyperion[Year], Hyperion[Period])
)

代码逻辑说明 #

• 期间数字转换：把P01这类字符串转换为数字（比如1），避免字符串比较的潜在问题（比如P10和P2的字符串比较错误）。
• 跨年逻辑处理：当选中的是P01/P02时，自动计算上一年的对应起始期间（P01对应上一年P11，P02对应上一年P12）。
• 保留其他筛选：ALLSELECTED(Hyperion[Year], Hyperion[Period])确保Account等其他字段的筛选依然生效，只会覆盖年份和期间的筛选。

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