如何使用DAX实现按业务案例最新生效状态的数值统计

前置准备 #

• 你需要一张独立的日期表（Calendar），不要和你的业务表（下文统一命名为CaseRecords）建立物理关联，日期表覆盖你需要计算的所有日期范围，示例中结束日期为2021/9/8。
• 请先将业务表CaseRecords的Date字段转换为标准日期格式，不要用文本格式存储日期。

度量值编写 #

逻辑核心为：针对当前行上下文的「日期+案例ID」组合，先定位该案例在当前日期之前的最后一次变更记录，再读取该记录对应的状态/金额值。

1. 生效状态度量值 #

生效Status_ID = 
VAR CurrentDate = MAX('Calendar'[Date]) // 读取当前上下文的日期
VAR CurrentCase = SELECTEDVALUE(CaseRecords[Case_ID]) // 读取当前上下文的案例ID
// 定位该案例在当前日期前最后一次变更的日期
VAR LastChangedDate = 
    CALCULATE(
        MAX(CaseRecords[Date]),
        CaseRecords[Date] <= CurrentDate,
        CaseRecords[Case_ID] = CurrentCase,
        REMOVEFILTERS('Calendar') // 清除日期表筛选，读取所有业务变更记录
    )
// 读取最后一次变更对应的状态值
VAR Result = 
    CALCULATE(
        SELECTEDVALUE(CaseRecords[Status_ID]),
        CaseRecords[Date] = LastChangedDate,
        CaseRecords[Case_ID] = CurrentCase,
        REMOVEFILTERS('Calendar')
    )
// 若当前日期早于该案例的首次变更日期，返回空值不显示
RETURN IF(ISBLANK(LastChangedDate), BLANK(), Result)

2. 生效金额度量值 #

逻辑和状态度量值完全一致，仅更换取值字段即可：

生效Amount = 
VAR CurrentDate = MAX('Calendar'[Date])
VAR CurrentCase = SELECTEDVALUE(CaseRecords[Case_ID])
VAR LastChangedDate = 
    CALCULATE(
        MAX(CaseRecords[Date]),
        CaseRecords[Date] <= CurrentDate,
        CaseRecords[Case_ID] = CurrentCase,
        REMOVEFILTERS('Calendar')
    )
VAR Result = 
    CALCULATE(
        SELECTEDVALUE(CaseRecords[Amount]),
        CaseRecords[Date] = LastChangedDate,
        CaseRecords[Case_ID] = CurrentCase,
        REMOVEFILTERS('Calendar')
    )
RETURN IF(ISBLANK(LastChangedDate), BLANK(), Result)

结果输出方法 #

将日期表的Date字段、业务表的Case_ID字段放到表格视觉对象的行维度，再把上述两个度量值放到值区域，即可得到你示例的输出结果。

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