Power BI中折线图整合实际值与预测值的两类问题求助

现有DAX度量值 #

2025 & Forecast = 
VAR CurrentMonth = MAX('Calendar'[Month Number])

-- Actual 2025 value
VAR Actual2025 = CALCULATE(
    SUM(Forecast[Costs]),
    'Calendar'[Year] = 2025,
    'Calendar'[Month Number] = CurrentMonth
)

-- Forecasted value for 2025 (reuse the existing Forecast measure)
VAR Forecasted2025 = [Forecast Engine]

-- Total logic
VAR TotalMerged =
    IF(
        ISINSCOPE('Calendar'[Month Number]),
        COALESCE(Actual2025, Forecasted2025),
        SUMX(
            VALUES('Calendar'[Month Number]),
            VAR _Month = [Month Number] 
            VAR _Actual2025 = CALCULATE(SUM(Forecast[Costs]), 'Calendar'[Year] = 2025, 'Calendar'[Month Number] = _Month)

            -- Use the forecast measure instead of recalculating
            VAR _Forecast = CALCULATE([Forecast Engine], 'Calendar'[Month Number] = _Month)

            RETURN COALESCE(_Actual2025, _Forecast)
        )
    )

RETURN TotalMerged

日历表DAX代码 #

Calendar = 
ADDCOLUMNS (
    CALENDARAUTO(),
    "Year", YEAR ( [Date] ),
    "Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1,
    "Month Name", FORMAT ( [Date], "mmmm" ),
    "Month Number", MONTH ( [Date] ),
    "Day", DAY ( [Date] )
)

问题一：当前月份仅显示部分实际值，未包含剩余日期预测值 #

原因

现有逻辑使用COALESCE(Actual2025, Forecasted2025)，只要当前月份存在实际数据（哪怕仅前12天），就直接取实际值总和，不会叠加剩余日期的预测值，导致数值偏低。

解决方案

修改当前月份的计算逻辑，拆分已发生天数的实际值和剩余天数的预测值并求和：

2025 & Forecast = 
VAR CurrentDate = TODAY()
VAR CurrentYear = YEAR(CurrentDate)
VAR CurrentMonth = MONTH(CurrentDate)
VAR CurrentDay = DAY(CurrentDate)

-- 当前月份已发生的实际成本
VAR ActualCurrentMonth = CALCULATE(
    SUM(Forecast[Costs]),
    'Calendar'[Year] = CurrentYear,
    'Calendar'[Month Number] = CurrentMonth,
    'Calendar'[Day] <= CurrentDay
)

-- 当前月份剩余天数的预测成本
VAR ForecastRemainingCurrentMonth = CALCULATE(
    [Forecast Engine],
    'Calendar'[Year] = CurrentYear,
    'Calendar'[Month Number] = CurrentMonth,
    'Calendar'[Day] > CurrentDay
)

-- 非当前月份逻辑：有实际用实际，无实际用预测
VAR NonCurrentMonthLogic = 
    SUMX(
        VALUES('Calendar'[Month Number]),
        VAR _Month = [Month Number]
        VAR _Actual = CALCULATE(SUM(Forecast[Costs]), 'Calendar'[Year] = 2025, 'Calendar'[Month Number] = _Month)
        VAR _Forecast = CALCULATE([Forecast Engine], 'Calendar'[Year] = 2025, 'Calendar'[Month Number] = _Month)
        RETURN COALESCE(_Actual, _Forecast)
    )

VAR TotalMerged =
    IF(
        ISINSCOPE('Calendar'[Month Number]),
        IF(
            MAX('Calendar'[Year]) = CurrentYear && MAX('Calendar'[Month Number]) = CurrentMonth,
            ActualCurrentMonth + ForecastRemainingCurrentMonth,
            COALESCE(
                CALCULATE(SUM(Forecast[Costs]), 'Calendar'[Year] = 2025, 'Calendar'[Month Number] = MAX('Calendar'[Month Number])),
                CALCULATE([Forecast Engine], 'Calendar'[Year] = 2025, 'Calendar'[Month Number] = MAX('Calendar'[Month Number]))
            )
        ),
        NonCurrentMonthLogic
    )

RETURN TotalMerged

问题二：折线图中预测值显示在2024年，不符合预期 #

原因

1. [Forecast Engine]未明确指定2025年筛选，导致上下文继承了2024年的年份条件；
2. 日历表与Forecast表的日期关联存在偏差，或Forecast数据的年份标记错误；
3. 现有代码调用[Forecast Engine]时未强制限定2025年，导致预测值被分配到其他年份。

解决方案

1. 强制指定预测值的年份筛选：在所有调用[Forecast Engine]的位置，添加'Calendar'[Year] = 2025条件，确保预测值仅计算2025年数据；
2. 检查[Forecast Engine]内部逻辑：确认该度量值是否存在硬编码年份或依赖上下文年份的情况，若有则调整为适配2025年；
3. 验证数据关联：检查Calendar表与Forecast表的日期关联是否正确，确保2025年的Forecast数据能匹配到Calendar表的对应日期。

核心修改示例：

VAR _Forecast = CALCULATE([Forecast Engine], 'Calendar'[Year] = 2025, 'Calendar'[Month Number] = _Month)

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