PAYATHON 2026

按月预测 payment 并在 Need Date 停止计算

支付小周

结论

先建立一张独立的月份表，再根据以下条件判断订单是否应在某个月产生 forecast：

MonthStart >= OrderMonth
AND MonthStart <= NeedMonth

这里要使用 <= NeedMonth，因为预期结果包含 Need Date 所在月份。也就是说，payment 从 Need Date 的下一个月开始停止，而不是进入 Need Date 月时停止。

在 Power BI 中，可以用月份表和 DAX Measure 完成这一计算。如果数据量很大，或者展开后的明细还要提供给其他系统使用，更适合在 SQL 中建立月份维表并预先展开。

不过，示例中的分摊公式需要进一步确认：

  • 7/1/2024 到 12/1/2024 的月份差是 5。
  • 如果首尾月份都参与计算，实际会产生 6 期 payment。
  • 按 $650 / 5 = $130 分摊 6 期，最终总额是 $780，超过订单成本 $650。

因此，这里有两种业务规则：

  1. 严格复现 Desired Output：除数使用月份差，同时保留 Need Date 月。这样计算出的累计金额可能超过订单总成本。
  2. 确保累计金额等于订单总成本：除数使用包含首尾月份的期数，也就是 月份差 + 1。

下面先按照题目中的 Desired Output 实现，再说明如何调整为金额守恒的分摊方式。

Power BI 实现

1. 创建月份表

不要依赖订单事实表中已有的日期。月份表应覆盖 forecast 所需的最远日期，例如 2029 年。

Forecast Months =
VAR StartDate =
    DATE(
        YEAR(MINX(ALL(Orders), Orders[Order Date])),
        MONTH(MINX(ALL(Orders), Orders[Order Date])),
        1
    )
VAR EndDate =
    DATE(2029, 12, 1)
RETURN
DISTINCT(
    SELECTCOLUMNS(
        CALENDAR(StartDate, EOMONTH(EndDate, 0)),
        "Month Start", DATE(YEAR([Date]), MONTH([Date]), 1)
    )
)

如果订单中的 Need Date 能可靠覆盖全部未来计划，也可以动态计算结束月份：

VAR EndDate =
    EOMONTH(MAXX(ALL(Orders), Orders[Need Date]), 0)

如果图表需要在没有 forecast 的情况下继续显示到固定年份，应保留固定的结束日期，或者用参数表维护 forecast horizon。

建议将 Forecast Months 作为独立的月份轴，不要让它与 Orders[Order Date] 建立活动关系。否则，图表中的某个月可能只保留当月下单的订单，之前订单产生的后续 payment 会被过滤掉。

2. 创建每月分摊金额列

以下公式严格按照题目给出的计算规则处理：

Calculated Cost Per Month =
VAR OrderMonth =
    DATE(YEAR(Orders[Order Date]), MONTH(Orders[Order Date]), 1)
VAR NeedMonth =
    DATE(YEAR(Orders[Need Date]), MONTH(Orders[Need Date]), 1)
VAR MonthDifference =
    DATEDIFF(OrderMonth, NeedMonth, MONTH)
RETURN
IF(
    NeedMonth < OrderMonth,
    BLANK(),
    IF(
        MonthDifference = 0,
        Orders[Total Item Cost],
        DIVIDE(Orders[Total Item Cost], MonthDifference)
    )
)

同月订单需要单独处理，以免除以 0。因此，示例中的 Order 1-3 会在 2024 年 9 月产生 $300。

3. 创建 forecast Measure

Forecast Cost =
VAR SelectedMonth =
    SELECTEDVALUE('Forecast Months'[Month Start])
RETURN
SUMX(
    FILTER(
        Orders,
        VAR OrderMonth =
            DATE(YEAR(Orders[Order Date]), MONTH(Orders[Order Date]), 1)
        VAR NeedMonth =
            DATE(YEAR(Orders[Need Date]), MONTH(Orders[Need Date]), 1)
        RETURN
            NOT ISBLANK(SelectedMonth)
                && SelectedMonth >= OrderMonth
                && SelectedMonth <= NeedMonth
    ),
    Orders[Calculated Cost Per Month]
)

边界条件是：

SelectedMonth <= NeedMonth

如果写成 < NeedMonth，Need Date 当月不会产生 payment，与 Desired Output 不符。

该 Measure 没有使用 ALL(Orders)，所以项目、订单和部门等现有筛选条件仍然有效。

4. 配置图表

折线图可以这样设置：

  • X 轴：Forecast Months[Month Start]
  • Y 轴：[Forecast Cost]
  • 图例：根据需要放入项目或订单字段

如果要显示没有支出的月份，可以为 X 轴字段开启“显示无数据的项”，也可以让 Measure 返回 0：

Forecast Cost Display =
COALESCE([Forecast Cost], 0)

按题目数据计算，结果如下：

MonthForecast Cost
2024-07-01130
2024-08-01730
2024-09-011030
2024-10-01730
2024-11-01130
2024-12-01130

保证分摊总额等于订单成本

如果 $650 必须在 7 月至 12 月之间完整分摊且不能超额，期数应定义为：

Payment Months = DATEDIFF(OrderMonth, NeedMonth, MONTH) + 1

对应的计算列如下：

Calculated Cost Per Month Balanced =
VAR OrderMonth =
    DATE(YEAR(Orders[Order Date]), MONTH(Orders[Order Date]), 1)
VAR NeedMonth =
    DATE(YEAR(Orders[Need Date]), MONTH(Orders[Need Date]), 1)
VAR PaymentMonths =
    DATEDIFF(OrderMonth, NeedMonth, MONTH) + 1
RETURN
IF(
    NeedMonth < OrderMonth,
    BLANK(),
    DIVIDE(Orders[Total Item Cost], PaymentMonths)
)

计算结果为：

  • Order 1-1：650 / 6 = 108.333...
  • Order 1-2：1200 / 3 = 400
  • Order 1-3：300 / 1 = 300

这些每月金额与题目中的数字不同，但每个订单的累计分摊额会准确等于 Total Item Cost。实际建模前，需要由业务方明确采用哪一种口径。

SQL Server 实现

数据量较大时，可以在 SQL 中建立永久月份维表。下面使用递归 CTE 展示完整逻辑，生产环境通常更适合维护一个可复用的 DimMonth 表。

DECLARE @ForecastEnd date = '2029-12-01';

WITH DateBounds AS
(
    SELECT
        DATEFROMPARTS(
            YEAR(MIN(OrderDate)),
            MONTH(MIN(OrderDate)),
            1
        ) AS StartMonth
    FROM dbo.Orders
),
Months AS
(
    SELECT StartMonth AS MonthStart
    FROM DateBounds

    UNION ALL

    SELECT DATEADD(MONTH, 1, MonthStart)
    FROM Months
    WHERE MonthStart < @ForecastEnd
),
OrderAllocation AS
(
    SELECT
        o.OrderNum,
        DATEFROMPARTS(
            YEAR(o.OrderDate),
            MONTH(o.OrderDate),
            1
        ) AS OrderMonth,
        DATEFROMPARTS(
            YEAR(o.NeedDate),
            MONTH(o.NeedDate),
            1
        ) AS NeedMonth,
        CASE
            WHEN DATEDIFF(MONTH, o.OrderDate, o.NeedDate) = 0
                THEN CAST(o.TotalItemCost AS decimal(19, 4))
            ELSE
                CAST(o.TotalItemCost AS decimal(19, 4))
                / NULLIF(DATEDIFF(MONTH, o.OrderDate, o.NeedDate), 0)
        END AS CostPerMonth
    FROM dbo.Orders AS o
    WHERE o.NeedDate >= o.OrderDate
)
SELECT
    m.MonthStart,
    SUM(a.CostPerMonth) AS TotalForecastCost
FROM Months AS m
LEFT JOIN OrderAllocation AS a
    ON m.MonthStart >= a.OrderMonth
   AND m.MonthStart <= a.NeedMonth
GROUP BY
    m.MonthStart
ORDER BY
    m.MonthStart
OPTION (MAXRECURSION 0);

SQL 中是否包含 Need Date 月，同样由以下条件决定：

m.MonthStart <= a.NeedMonth

如果需要返回每个订单在每个月的明细，而不是直接汇总，可以使用：

SELECT
    a.OrderNum,
    m.MonthStart,
    a.CostPerMonth
FROM Months AS m
INNER JOIN OrderAllocation AS a
    ON m.MonthStart >= a.OrderMonth
   AND m.MonthStart <= a.NeedMonth
ORDER BY
    a.OrderNum,
    m.MonthStart
OPTION (MAXRECURSION 0);

如果采用金额守恒的分摊方式，只需将 SQL 中的金额表达式改为：

CAST(o.TotalItemCost AS decimal(19, 4))
/ NULLIF(DATEDIFF(MONTH, o.OrderDate, o.NeedDate) + 1, 0)

Power BI 还是 SQL

当数据量中等，而且 forecast 只用于 Power BI 展示时，可以优先采用独立月份表加 DAX Measure。这样模型更灵活，也不会真正生成大量“订单数 × 月份数”的明细行。

以下情况更适合在 SQL 中预处理：

  • 订单数量很大；
  • 单个订单跨越多年；
  • 多个报表需要共用同一套分摊结果；
  • 分摊规则复杂，需要集中管理；
  • 需要导出订单与月份粒度的 forecast 明细。

无论在哪一层实现，都要统一三个定义：日期是否按月初归一化、是否包含 Need Date 月，以及分摊总额是否必须等于订单成本。当前 Desired Output 包含 Need Date 月，但使用的除数与实际包含的月份数量不一致。这个业务口径需要在上线前确认。

备注：内容仅供参考。