如何按 FIFO 将每笔 Payment 金额分配至 Invoice

结论

先汇总所有 Payment 和 Credit Memo 的负数金额，得到可分配额度。再按 Posting Date 和唯一序号对 Invoice 排序。每张发票的最终未结金额可按下面的公式计算：

Remaining Amount =
MIN(
    当前 Invoice 金额,
    MAX(截至当前 Invoice 的累计开票金额 - 全部可分配额度, 0)
)

这种算法会按照 FIFO 依次冲销最早的发票，无需逐笔拆分 Payment。

示例中的预期结果相差 0.2。所有发票合计 287351.6，Payment 和 Credit Memo 合计 244295，因此最终未结金额应为：

287351.6 - 244295 = 43056.6

所以，单据 2324PSI3778 的准确 Remaining Amount 是 12296.8，不是 12297。如果业务上需要显示整数，还要明确采用哪种舍入规则。

计算原理

所有负数交易提供的可分配额度是：

80000 + 44295 + 70000 + 50000 = 244295

按 FIFO 冲销后：

1. 第一张 Invoice 176866.8 全额结清。
2. 可用额度剩余 244295 - 176866.8 = 67428.2。
3. 第二张 Invoice 的金额是 79725，未结金额为： 79725 - 67428.2 = 12296.8。
4. 至此，可分配额度已经用完，后续 Invoice 的金额保持不变。

计算结果如下：

Payment 和 Credit Memo 行的 Remaining Amount 均设为 0。

Power BI / DAX 实现

建议先在 Power Query 中添加一个稳定且唯一的 Index 列。只按 Posting Date 排序并不可靠，因为同一天可能有多笔交易。

假设表名为 Transactions，可以创建以下计算列：

Remaining Amount =
VAR CurrentType =
    Transactions[Transaction Type]
VAR CurrentValue =
    Transactions[Value]
VAR CurrentIndex =
    Transactions[Index]

VAR TotalAvailableAmount =
    -CALCULATE(
        SUM(Transactions[Value]),
        FILTER(
            ALL(Transactions),
            Transactions[Transaction Type] IN {
                "Payment",
                "Credit Memo"
            }
        )
    )

VAR CumulativeInvoiceAmount =
    CALCULATE(
        SUM(Transactions[Value]),
        FILTER(
            ALL(Transactions),
            Transactions[Transaction Type] = "Invoice"
                && Transactions[Index] <= CurrentIndex
        )
    )

VAR OutstandingAtThisPoint =
    MAX(
        CumulativeInvoiceAmount - TotalAvailableAmount,
        0
    )

RETURN
    IF(
        CurrentType = "Invoice",
        MIN(CurrentValue, OutstandingAtThisPoint),
        0
    )

如果数据涉及多个客户、账户或币种，必须在同一分组内计算。例如按 Customer ID 分配时，需要在两个 FILTER 中都加入当前客户：

Remaining Amount =
VAR CurrentType =
    Transactions[Transaction Type]
VAR CurrentValue =
    Transactions[Value]
VAR CurrentIndex =
    Transactions[Index]
VAR CurrentCustomer =
    Transactions[Customer ID]

VAR TotalAvailableAmount =
    -CALCULATE(
        SUM(Transactions[Value]),
        FILTER(
            ALL(Transactions),
            Transactions[Customer ID] = CurrentCustomer
                && Transactions[Transaction Type] IN {
                    "Payment",
                    "Credit Memo"
                }
        )
    )

VAR CumulativeInvoiceAmount =
    CALCULATE(
        SUM(Transactions[Value]),
        FILTER(
            ALL(Transactions),
            Transactions[Customer ID] = CurrentCustomer
                && Transactions[Transaction Type] = "Invoice"
                && Transactions[Index] <= CurrentIndex
        )
    )

RETURN
    IF(
        CurrentType = "Invoice",
        MIN(
            CurrentValue,
            MAX(CumulativeInvoiceAmount - TotalAvailableAmount, 0)
        ),
        0
    )

SQL 实现

如果在数据库中处理，可以先计算发票累计金额，再通过窗口函数完成 FIFO 分配：

WITH ordered_transactions AS (
    SELECT
        t.*,
        SUM(
            CASE
                WHEN transaction_type = 'Invoice' THEN value
                ELSE 0
            END
        ) OVER (
            PARTITION BY customer_id
            ORDER BY posting_date, transaction_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_invoice_amount,

        SUM(
            CASE
                WHEN transaction_type IN ('Payment', 'Credit Memo')
                    THEN -value
                ELSE 0
            END
        ) OVER (
            PARTITION BY customer_id
        ) AS total_available_amount
    FROM transactions AS t
)
SELECT
    posting_date,
    transaction_type,
    document_no,
    value,
    CASE
        WHEN transaction_type <> 'Invoice' THEN 0
        ELSE LEAST(
            value,
            GREATEST(
                cumulative_invoice_amount - total_available_amount,
                0
            )
        )
    END AS remaining_amount
FROM ordered_transactions
ORDER BY customer_id, posting_date, transaction_id;

其中，transaction_id 必须能够确定同一天内各笔交易的先后顺序。不同数据库对 LEAST 和 GREATEST 的支持不同。如果所用数据库不支持这两个函数，可以改用嵌套的 CASE 表达式。

注意事项

• 示例结果表示使用全部 Payment 后的最终余额，而不是每个交易日期对应的历史余额。如果 Payment 只能冲销其日期之前已经存在的 Invoice，就需要按时间维护动态余额。
• FIFO 通常需要按客户、账户、币种和业务主体分别计算，不能混合不同客户或币种的金额。
• 同一天有多笔交易时，必须添加稳定的排序字段，例如流水号、创建时间或索引。
• 金额字段应使用定点小数类型，例如 SQL 的 DECIMAL 或 Power BI 的 Fixed decimal number，以免出现浮点误差。
• 最好只在最终展示时舍入。如果每次分配都进行舍入，累计结果可能与总账相差几分钱。
• 如果 Payment 总额超过所有 Invoice，总发票余额为 0。超出的金额应作为未分配收款单独计算，不能让 Invoice Remaining Amount 变成负数。

备注：内容仅供参考。