Power BI添加计算列读取上一行值实现月度收据需求预测

原M代码的核心错误 #

你之前写的Power Query M公式存在3个明确问题，无法匹配业务逻辑：

• 初始值规则错配：仅2013年1月（最早记录月，无前期历史数据）适用初始计算规则，原公式错误将初始规则套用到2013年所有月份
• 初始值公式错误：业务要求初始值为「年均收据发放量 * 对应月份平均发放占比」，原公式错误写为「年均发放量 + 年均发放量*当月占比」
• 递归逻辑缺失：业务要求从第二个月开始逐行依赖上月预测结果滚动计算，原公式错误引用上年发放量字段做静态计算，完全没有实现滚动递推逻辑

推荐实现方案（DAX计算列，性能最优） #

Power Query M语言本身不擅长逐行递归计算，数据量稍大就会出现严重卡顿，优先用DAX计算列实现，步骤如下：

1. 提前对事实表按年份、月份升序排序，保证时间顺序正确
2. 确认表中包含必备字段：月度日期字段/连续索引字段、AvgYearlyReceiptsIssued (All Years)（年均收据发放量）、AvgPercentageOfReceiptsIssuedThisMonth (All Years)（当月历史平均发放占比）、ReceiptsIssued（当月实际收据发放量）

写法1：关联标准日期表（推荐，鲁棒性最强） #

如果你的模型已经挂载了连续的标准日期表，直接用时间智能函数定位上月数据，DAX代码如下：

Prelim Anticipated Demand (All Years) = 
VAR CurrentDate = 'Receipts_Fact'[MonthDate]
VAR CurrentBase = 'Receipts_Fact'[AvgYearlyReceiptsIssued (All Years)] * 'Receipts_Fact'[AvgPercentageOfReceiptsIssuedThisMonth (All Years)]
VAR IsFirstPeriod = CurrentDate = DATE(2013,1,1)
VAR LastMonthForecast = 
    CALCULATE(
        SUM('Receipts_Fact'[Prelim Anticipated Demand (All Years)]),
        DATEADD('Date'[Date], -1, MONTH)
    )
VAR LastMonthActual = 
    CALCULATE(
        SUM('Receipts_Fact'[ReceiptsIssued]),
        DATEADD('Date'[Date], -1, MONTH)
    )
RETURN
IF(
    IsFirstPeriod,
    CurrentBase,
    CurrentBase + (LastMonthForecast - LastMonthActual)
)

写法2：无日期表场景（用索引列定位） #

如果没有建标准日期表，先在Power Query里给按年月排好序的表加从1开始的连续索引列，再用以下DAX代码：

Prelim Anticipated Demand (All Years) = 
VAR CurrentIdx = 'Receipts_Fact'[RowIndex]
VAR CurrentBase = 'Receipts_Fact'[AvgYearlyReceiptsIssued (All Years)] * 'Receipts_Fact'[AvgPercentageOfReceiptsIssuedThisMonth (All Years)]
VAR IsFirstRow = CurrentIdx = 1
VAR LastRowForecast = 
    CALCULATE(
        MAX('Receipts_Fact'[Prelim Anticipated Demand (All Years)]),
        FILTER(ALL('Receipts_Fact'), 'Receipts_Fact'[RowIndex] = CurrentIdx - 1)
    )
VAR LastRowActual = 
    CALCULATE(
        MAX('Receipts_Fact'[ReceiptsIssued]),
        FILTER(ALL('Receipts_Fact'), 'Receipts_Fact'[RowIndex] = CurrentIdx - 1)
    )
RETURN
IF(IsFirstRow, CurrentBase, CurrentBase + (LastRowForecast - LastRowActual))

备选方案（Power Query M实现，仅适合小数据量） #

如果必须在Power Query层完成计算，不要用普通自定义列逐行引用（M语言自定义列默认无法识别同表刚计算出的列值，会触发循环引用报错），要用List.Generate做批量递归计算，核心代码如下：

let
    Source = #"Added Custom3",
    // 按年月升序排序，保证时间顺序正确
    SortedTable = Table.Sort(Source,{{"Year", Order.Ascending}, {"Month", Order.Ascending}}),
    // 提前计算每行的基准值
    AddBaseCol = Table.AddColumn(SortedTable, "BaseValue", each [AvgYearlyReceiptsIssued (All Years)] * [AvgPercentageOfReceiptsIssuedThisMonth (All Years)]),
    // 把计算需要的字段转为列表，提升递归计算性能
    BaseList = AddBaseCol[BaseValue],
    ActualList = AddBaseCol[ReceiptsIssued],
    TotalRows = Table.RowCount(AddBaseCol),
    // 递归生成所有月份的预测值
    ForecastList = List.Generate(
        () => [ForecastVal = BaseList{0}, RowIdx = 0],
        each [RowIdx] < TotalRows,
        each [
            ForecastVal = if [RowIdx] = TotalRows -1 then null else BaseList{[RowIdx]+1} + ([ForecastVal] - ActualList{[RowIdx]}),
            RowIdx = [RowIdx] + 1
        ],
        each [ForecastVal]
    ),
    // 把预测结果合并回原表
    CombineResult = Table.FromColumns(Table.ToColumns(SortedTable) & {ForecastList}, Table.ColumnNames(SortedTable) & {"Prelim Anticipated Demand (All Years)"})
in
    CombineResult

逻辑校验规则 #

计算完成后可按以下规则验证结果正确性：

• 2013年1月预测值 = 对应年均发放量 * 1月平均发放占比，无额外调整项
• 2013年2月预测值 = （年均发放量*2月平均发放占比） + （2013年1月预测值 - 2013年1月实际发放量）
• 后续所有月份均按上述递推规则计算，不会出现初始规则跨月套用的问题

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