PAYATHON 2026

如何在 DAX model 中计算 invoice running balance？

支付老李

结论

不必为每张发票每天生成一条余额快照。发票在指定日期的余额可以用 DAX 动态计算：

[ \text{Balance as of date}

\text{Invoice Amount}

\sum(\text{Payments where Payment Date} \le \text{Selected Date}) ]

但当前 Date[Date] 与 Invoices[expiry date] 之间是 active relationship，日期切片器会先按到期日筛选发票，这与“查看任意日期的实际余额”不符。建议把这条关系改为 inactive relationship。分析到期日时，再在相应的度量值中使用 USERELATIONSHIP 激活关系。

基本实现

以下示例基于这些假设：

  • Invoices[invoice id] 唯一标识一张发票；
  • 发票金额为正数；
  • Payments[amount] 为正数；
  • Invoices[invoice id] 与 Payments[invoice id] 是一对多关系，过滤方向为 Invoices 到 Payments；
  • 报表使用 'Date'[Date] 作为日期字段；
  • Date 与 Invoices[expiry date] 的关系已改为 inactive。

创建以下度量值：

Invoice Balance As Of :=
VAR AsOfDate =
    MAX ( 'Date'[Date] )
VAR InvoiceDate =
    SELECTEDVALUE ( Invoices[invoice date] )
VAR InvoiceAmount =
    SUM ( Invoices[amount] )
VAR PaymentsToDate =
    CALCULATE (
        SUM ( Payments[amount] ),
        FILTER (
            ALL ( Payments[payment date] ),
            Payments[payment date] <= AsOfDate
        )
    )
RETURN
    IF (
        ISBLANK ( AsOfDate )
            || ISBLANK ( InvoiceDate )
            || InvoiceDate > AsOfDate,
        BLANK (),
        InvoiceAmount - COALESCE ( PaymentsToDate, 0 )
    )

把 Invoices[invoice id] 放在表格或矩阵的行中，并加入这个度量值。日期切片器选定某一天后，度量值会取得所选日期，判断发票当时是否已经开具，然后汇总截至该日发生的付款，并从发票金额中扣除累计付款金额。

尚未开具的发票会返回 BLANK()，不会被算作未付款余额。

正确处理合计行

上面的写法适合逐张展示发票。不过，合计行可能包含多张发票，此时 SELECTEDVALUE(Invoices[invoice date]) 会返回空值。要正确计算总余额，可以逐张发票计算后再汇总：

Invoice Balance As Of :=
VAR AsOfDate =
    MAX ( 'Date'[Date] )
RETURN
    SUMX (
        VALUES ( Invoices[invoice id] ),
        VAR InvoiceDate =
            CALCULATE ( MAX ( Invoices[invoice date] ) )
        VAR InvoiceAmount =
            CALCULATE ( SUM ( Invoices[amount] ) )
        VAR PaymentsToDate =
            CALCULATE (
                SUM ( Payments[amount] ),
                FILTER (
                    ALL ( Payments[payment date] ),
                    Payments[payment date] <= AsOfDate
                )
            )
        RETURN
            IF (
                InvoiceDate <= AsOfDate,
                InvoiceAmount - COALESCE ( PaymentsToDate, 0 ),
                BLANK ()
            )
    )

这个版本既可以显示单张发票的余额，也可以正确汇总当前筛选范围内所有发票的余额。

如果不能修改 active relationship

如果必须保留 Date[Date] 到 Invoices[expiry date] 的 active relationship，可以在计算度量值时暂时禁用它：

Invoice Balance As Of :=
VAR AsOfDate =
    MAX ( 'Date'[Date] )
VAR InvoiceSet =
    CALCULATETABLE (
        VALUES ( Invoices[invoice id] ),
        CROSSFILTER (
            'Date'[Date],
            Invoices[expiry date],
            NONE
        )
    )
RETURN
    SUMX (
        InvoiceSet,
        VAR InvoiceDate =
            CALCULATE (
                MAX ( Invoices[invoice date] ),
                CROSSFILTER (
                    'Date'[Date],
                    Invoices[expiry date],
                    NONE
                )
            )
        VAR InvoiceAmount =
            CALCULATE (
                SUM ( Invoices[amount] ),
                CROSSFILTER (
                    'Date'[Date],
                    Invoices[expiry date],
                    NONE
                )
            )
        VAR PaymentsToDate =
            CALCULATE (
                SUM ( Payments[amount] ),
                CROSSFILTER (
                    'Date'[Date],
                    Invoices[expiry date],
                    NONE
                ),
                FILTER (
                    ALL ( Payments[payment date] ),
                    Payments[payment date] <= AsOfDate
                )
            )
        RETURN
            IF (
                InvoiceDate <= AsOfDate,
                InvoiceAmount - COALESCE ( PaymentsToDate, 0 ),
                BLANK ()
            )
    )

这种做法有一个限制：active relationship 仍可能在生成可视化行项目时，过滤掉到期日不匹配的发票。度量值能改变计算上下文，但不一定能让已经从行分组中排除的 invoice id 重新出现。因此，无论从模型语义还是可视化行为来看，把到期日关系设为 inactive 通常都更可靠。

需要按到期日分析时，可以单独创建一个度量值：

Invoice Amount By Expiry Date :=
CALCULATE (
    SUM ( Invoices[amount] ),
    USERELATIONSHIP (
        'Date'[Date],
        Invoices[expiry date]
    )
)

日期范围的处理

示例使用：

MAX ( 'Date'[Date] )

如果切片器选择了一个日期范围，余额会按范围内的最后一天计算，这通常符合“截至日期”的含义。

如果业务要求只能选择一个日期，可以改用：

SELECTEDVALUE ( 'Date'[Date] )

在没有唯一日期时返回空值或提示：

IF (
    NOT HASONEVALUE ( 'Date'[Date] ),
    BLANK (),
    -- 余额计算
)

到期日与余额不是同一概念

expiry date 通常指付款到期日，并不决定发票余额是否存在。发票到期后，只要还未付清，余额就仍然存在。

如果要计算“指定日期已经逾期的余额”，还需要增加以下判断：

InvoiceDate <= AsOfDate
    && ExpiryDate < AsOfDate

例如：

Overdue Invoice Balance As Of :=
VAR AsOfDate =
    MAX ( 'Date'[Date] )
RETURN
    SUMX (
        VALUES ( Invoices[invoice id] ),
        VAR InvoiceDate =
            CALCULATE ( MAX ( Invoices[invoice date] ) )
        VAR ExpiryDate =
            CALCULATE ( MAX ( Invoices[expiry date] ) )
        VAR InvoiceAmount =
            CALCULATE ( SUM ( Invoices[amount] ) )
        VAR PaymentsToDate =
            CALCULATE (
                SUM ( Payments[amount] ),
                FILTER (
                    ALL ( Payments[payment date] ),
                    Payments[payment date] <= AsOfDate
                )
            )
        RETURN
            IF (
                InvoiceDate <= AsOfDate
                    && ExpiryDate < AsOfDate,
                InvoiceAmount - COALESCE ( PaymentsToDate, 0 ),
                BLANK ()
            )
    )

退款、贷项通知单、付款冲销和超额付款采用什么正负号，还需要根据实际业务确定。DAX 只按日期累计金额，最终余额能否为负数取决于财务口径。

备注：内容仅供参考。