Power BI中2022年后基于前季度值递减排减的DAX实现问询

前提准备 #

首先确保你的Power BI模型中有标记为日期表的日期维度表，且该表与[Actual]表、[Project Impacts]表建立正确的多对一关系。日期表需包含季度相关字段（如季度开始/结束日期、年季度标识），这是时间智能函数生效的基础。

核心DAX度量值 #

以下度量值分两个阶段计算Forecast Values，解决2023年后无实际值导致的负数问题：

Forecast Values = 
VAR CurrentQuarterStart = STARTOFQUARTER('Date'[Date])
VAR Q4_2022_End = ENDOFQUARTER(DATE(2022, 12, 31))

-- 计算2022年第四季度的Forecast基准值（实际值减当季项目节省）
VAR BaselineForecast = 
    CALCULATE(
        [Actual Values] - [Estimated saving from projects],
        DATESINPERIOD('Date'[Date], Q4_2022_End, -1, QUARTER)
    )

-- 计算2023年第一季度至当前季度的累计项目节省总和
VAR TotalPost2022Savings = 
    CALCULATE(
        [Estimated saving from projects],
        DATESBETWEEN(
            'Date'[Date],
            STARTOFQUARTER(DATE(2023, 1, 1)),
            CurrentQuarterStart
        )
    )

RETURN
    IF(
        -- 2022年底前：当季实际值减去当季项目节省
        CurrentQuarterStart <= Q4_2022_End,
        CALCULATE([Actual Values], DATESINPERIOD('Date'[Date], CurrentQuarterStart, 1, QUARTER)) 
        - CALCULATE([Estimated saving from projects], DATESINPERIOD('Date'[Date], CurrentQuarterStart, 1, QUARTER)),
        -- 2023年后：用2022Q4的基准值减去2023年以来的累计项目节省
        BaselineForecast - TotalPost2022Savings
    )

关键逻辑说明 #

1. 2022年底前的计算：直接取当季度的实际值聚合结果，减去当季度的项目影响值，得到该季度的Forecast。
2. 2023年后的计算：以2022Q4的Forecast作为基准值，后续每个季度的Forecast等于基准值减去从2023Q1到当前季度的累计项目节省（等价于递推式的上季度Forecast - 当季节省）。
3. 避免负数问题：不再依赖2023年后不存在的实际值，而是基于2022Q4的有效基准值进行递推，彻底解决空值减正数导致的负数结果。

额外优化建议 #

• 如果你的仪表盘需要月度粒度的Forecast，需将季度项目节省分配到月度（例如平均分配到季度内的3个月），调整后的DAX可将TotalPost2022Savings替换为月度累计分配值，同时将时间粒度从季度改为月度。
• 若[Actual Values]度量值的聚合方式需调整（如取平均而非求和），直接修改度量值内的聚合逻辑即可。

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