Power BI度量值QTD与YTD计算异常排查求助

• 配置了三个切片器：Period（选项为'MTD'/'QTD'/'YTD'）、Year、Month
• 选中QTD+指定年份+2月时，预期累加1月和2月数据，但实际仅计算2月数据。例如Disbursement KPI，1月数值为3.63 B，选中2月后正确值应为7.06 B（3.63+3.53），但度量值显示3.53 B
• 调试确认日期参数（SelectedYear、SelectedMonth、QuarterStartDate等）取值正常

原度量值代码 #

Ach. QTD 6 = 
VAR SelectedPeriod = SELECTEDVALUE('P. Table'[Period])
VAR SelectedMonth = SELECTEDVALUE(PMS_BW[Month])
VAR SelectedYear = SELECTEDVALUE(PMS_BW[Year])
VAR SelectedDate = DATE(SelectedYear, SelectedMonth, 1)
VAR EOM = EOMONTH(SelectedDate, 0)

VAR QuarterStartDate = 
    SWITCH(
        TRUE(),
        SelectedMonth <= 3, DATE(SelectedYear, 1, 1),
        SelectedMonth <= 6, DATE(SelectedYear, 4, 1),
        SelectedMonth <= 9, DATE(SelectedYear, 7, 1),
        DATE(SelectedYear, 10, 1)
    )

VAR Val = 
    SWITCH(
        SelectedPeriod,
        "MTD", 
            CALCULATE(SUM(PMS_BW[Ach_CM]), 
                PMS_BW[BusinessDate] <= EOM && PMS_BW[BusinessDate] >= SelectedDate
            ),
        "QTD", 
            CALCULATE(SUM(PMS_BW[Ach_CM]), 
                PMS_BW[BusinessDate] <= EOM && PMS_BW[BusinessDate] >= QuarterStartDate
            ),
        "YTD", 
            CALCULATE(SUM(PMS_BW[Ach_CM]), 
                PMS_BW[BusinessDate] <= EOM && PMS_BW[BusinessDate] >= DATE(SelectedYear, 1, 1)
            ),
        SUM(PMS_BW[Ach_CM])
    )

VAR Denom = 
    SWITCH(
        SelectedPeriod,
        "MTD", 
            CALCULATE(SUM(PMS_BW[Recovered_CM]), 
                PMS_BW[BusinessDate] <= EOM && PMS_BW[BusinessDate] >= SelectedDate
            ),
        "QTD", 
            CALCULATE(SUM(PMS_BW[Recovered_CM]), 
                PMS_BW[BusinessDate] <= EOM && PMS_BW[BusinessDate] >= QuarterStartDate
            ),
        "YTD", 
            CALCULATE(SUM(PMS_BW[Recovered_CM]), 
                PMS_BW[BusinessDate] <= EOM && PMS_BW[BusinessDate] >= DATE(SelectedYear, 1, 1)
            ),
        SUM(PMS_BW[Recovered_CM])
    )

VAR FVal =
    SWITCH(
        TRUE(),
        SELECTEDVALUE(PMS_BW[KPI]) IN {"CCR"},
            ROUND(DIVIDE(Denom, Val, 0) * 100, 2) & " %",
        SELECTEDVALUE(PMS_BW[KPI]) IN {"Retail", "Corporate"},
            ROUND(DIVIDE(Val, Denom, 0) * 100, 2) & " %",
        SELECTEDVALUE(PMS_BW[KPI]) IN {"Disbursement", "GLP", "NPL", "Green Financing", "SME", "CA", "SA", "TD", "Total Deposits"},
            SWITCH(
                TRUE(),
                Val >= 1000000000, FORMAT(Val / 1000000000, "#,0.00") & " B",
                Val >= 1000000, FORMAT(Val / 1000000, "#,0.00") & " M",
                FORMAT(Val, "#,0")
            ),
        FORMAT(Val, "#,0")
    )

RETURN FVal

问题根源 #

切片器选中Month=2时，PMS_BW表已经被筛选为仅包含2月的数据。即便在CALCULATE中指定了日期范围包含1月，也只能在已过滤的数据集内计算，无法获取1月的数值，导致QTD只计算了选中的2月数据。

解决方案 #

在CALCULATE函数中添加ALL(PMS_BW[Month])清除月份的筛选上下文，同时保留Year和Period的筛选条件。修改后的Val和Denom变量中QTD、YTD部分如下：

修改后的Val变量片段 #

"QTD", 
    CALCULATE(SUM(PMS_BW[Ach_CM]), 
        ALL(PMS_BW[Month]), // 清除月份筛选
        PMS_BW[BusinessDate] <= EOM && PMS_BW[BusinessDate] >= QuarterStartDate
    ),
"YTD", 
    CALCULATE(SUM(PMS_BW[Ach_CM]), 
        ALL(PMS_BW[Month]), // 清除月份筛选
        PMS_BW[BusinessDate] <= EOM && PMS_BW[BusinessDate] >= DATE(SelectedYear, 1, 1)
    ),

修改后的Denom变量片段 #

"QTD", 
    CALCULATE(SUM(PMS_BW[Recovered_CM]), 
        ALL(PMS_BW[Month]), // 清除月份筛选
        PMS_BW[BusinessDate] <= EOM && PMS_BW[BusinessDate] >= QuarterStartDate
    ),
"YTD", 
    CALCULATE(SUM(PMS_BW[Recovered_CM]), 
        ALL(PMS_BW[Month]), // 清除月份筛选
        PMS_BW[BusinessDate] <= EOM && PMS_BW[BusinessDate] >= DATE(SelectedYear, 1, 1)
    ),

额外建议：使用专门的日期表（Date Table）管理时间维度，结合Power BI内置的时间智能函数（如STARTOFQUARTER、DATESQTD）来处理QTD/YTD计算，这是更规范的时间度量实现方式，能避免手动计算日期范围的误差，同时更灵活处理筛选上下文。

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