Power BI中按月度取最新值计算YTD累计求和的技术问题

现有每日事实表，需创建DAX度量值计算YTD（年初至今）月度累计求和，但仅对每个月的最后一条有效观测值求和。使用Date日期表选择特定月份，Date Copy表展示所选月份之前的当年所有月份，Pred度量值用于提取每个月来自Prediction表的最新预测值。之前编写的Bad度量值返回结果不符合预期，寻求正确实现方案。

复现环境代码 #

Date日期表 #

Date = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2024, 12, 31 ) ),
    "Day", DAY ( [Date] ),
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "mmm" ),
    "Month Year", FORMAT ( [Date], "mmm yyyy" ),
    "EOM", EOMONTH ( [Date], 0 )
)

Date Copy表 #

Date Copy = 
SELECTCOLUMNS (
'Date',
"Date", 'Date'[Date],
"Year", 'Date'[Year],
"Month", 'Date'[Month],
"Month Number", 'Date'[Month Number]
)

Prediction事实表 #

Prediction = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2024, 3, 28 ) ),
    "Prediction",
        (
            1000 * DAY ( EOMONTH ( [Date], 0 ) ) - 10
        )
)

现有错误度量值（Bad） #

Bad = 
VAR LatestMonth =
    SELECTEDVALUE ( 'Date'[Month Number] )
VAR Tbl =
    SUMMARIZE (
        ALLSELECTED ( 'Date Copy' ),
        'Date Copy'[Month Number],
        'Date Copy'[Year],
        "Bad", [Pred]
    )
RETURN
    SUMX ( FILTER ( Tbl, [Month Number] <= LatestMonth ), [Pred] )

问题分析与正确实现 #

错误原因 #

原Bad度量值的核心问题在于：SUMX迭代时直接引用[Pred]会重新计算该度量值，而非使用SUMMARIZE中已生成的"Bad"列结果；同时未明确锁定当前选中年份，可能导致跨年度求和错误。

正确度量值实现 #

1. 优化后的YTD累计度量值

YTD_Latest_Monthly_Pred = 
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
VAR LatestMonth = SELECTEDVALUE('Date'[Month Number])
-- 按年、月分组，计算每个月的最新预测值
VAR MonthlyLatestPred = 
    SUMMARIZE(
        FILTER(ALLSELECTED('Date Copy'), 
            'Date Copy'[Year] = SelectedYear && 'Date Copy'[Month Number] <= LatestMonth
        ),
        'Date Copy'[Year],
        'Date Copy'[Month Number],
        "Monthly_Latest_Pred", [Pred]
    )
RETURN
    SUMX(MonthlyLatestPred, [Monthly_Latest_Pred])

2. 优化Pred度量值（确保单月最新值准确）

如果原Pred度量值存在上下文计算偏差，可替换为以下实现：

Pred = 
VAR CurrentYear = SELECTEDVALUE('Date Copy'[Year])
VAR CurrentMonth = SELECTEDVALUE('Date Copy'[Month Number])
-- 获取当前月最后一条有预测值的日期
VAR LastValidDate = 
    CALCULATE(
        MAX('Prediction'[Date]),
        FILTER('Prediction', 
            YEAR('Prediction'[Date]) = CurrentYear && MONTH('Prediction'[Date]) = CurrentMonth
        )
    )
RETURN
    CALCULATE(
        SUM('Prediction'[Prediction]),
        'Prediction'[Date] = LastValidDate,
        USERELATIONSHIP('Prediction'[Date], 'Date Copy'[Date])
    )

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