Power BI 按月末统计未支付交易并创建条形图

结论

建议单独建立一张“月末快照表”，再用度量值判断每笔交易在各月末是否尚未支付。

判断条件如下：

created_date < 次月第一天
并且
completed_date 为空，或 completed_date >= 次月第一天

这种方法会把以前月份创建、到当前月末仍未支付的交易保留下来。示例结果为：

• 202302：$10
• 202303：$110
• 202304：$100

如果只按 created_date 所属月份汇总，以前月份遗留的未支付交易不会进入后续月份。

1. 确认字段类型

假设交易表名为 Transactions，至少包含以下字段：

• Transactions[created_date]
• Transactions[completed_date]
• Transactions[amount]

先在 Power Query 或数据模型中检查字段类型：

• created_date 应为“日期”或“日期/时间”类型。
• completed_date 应为“日期”或“日期/时间”类型。
• 未支付记录应使用真正的 null，加载到 DAX 后会显示为 BLANK()。
• amount 应为数值或固定小数类型。

如果源数据使用空字符串表示空值，应先在 Power Query 中将空字符串转换为 null。

2. 创建月末快照表

在 Power BI 中选择“建模”→“新建表”，添加以下计算表：

SnapshotMonth =
VAR FirstCreatedDate =
    MINX (
        ALL ( Transactions ),
        Transactions[created_date]
    )
VAR FirstMonth =
    DATE (
        YEAR ( FirstCreatedDate ),
        MONTH ( FirstCreatedDate ),
        1
    )
VAR LastMonth =
    EOMONTH ( TODAY (), 0 )
RETURN
    DISTINCT (
        SELECTCOLUMNS (
            CALENDAR ( FirstMonth, LastMonth ),
            "MonthEnd", EOMONTH ( [Date], 0 ),
            "Period", FORMAT ( [Date], "yyyyMM" )
        )
    )

这段 DAX 将当前月份设为报告的最后一个月份。如果报告采用固定的分析范围，可以直接指定结束日期：

VAR LastMonth = DATE ( 2024, 12, 31 )

SnapshotMonth 是一张独立的快照表，不要将它与 Transactions 建立关系。它并不是用来筛选某个月创建的交易，而是为每个月提供一个“截至该月末”的计算日期。

建议将：

SnapshotMonth[Period]

设置为按以下字段排序：

SnapshotMonth[MonthEnd]

3. 创建未支付金额度量值

先创建基础金额度量值：

Total Amount =
SUM ( Transactions[amount] )

再创建月末未支付金额度量值：

Unpaid Amount at Month End =
VAR SelectedMonthEnd =
    MAX ( SnapshotMonth[MonthEnd] )
VAR NextMonthStart =
    SelectedMonthEnd + 1
RETURN
    CALCULATE (
        [Total Amount],
        FILTER (
            ALL (
                Transactions[created_date],
                Transactions[completed_date]
            ),
            Transactions[created_date] < NextMonthStart
                &&
                (
                    ISBLANK ( Transactions[completed_date] )
                    || Transactions[completed_date] >= NextMonthStart
                )
        )
    )

这段计算包含三层判断：

1. 交易的创建时间早于次月第一天，也就是在所选月份结束前已经存在。
2. 支付日期为空，说明交易尚未支付。
3. 支付日期为次月第一天或更晚，说明交易在所选月份结束时仍未支付。

用“次月第一天”作为边界比直接判断 <= MonthEnd 更可靠，因为日期字段中可能包含具体时间。例如：

2023-03-31 18:30:00

如果直接与 2023-03-31 00:00:00 比较，这笔交易可能会被排除。

4. 创建条形图

添加条形图或柱形图，并使用以下字段：

• 轴：SnapshotMonth[Period]
• 值：[Unpaid Amount at Month End]

然后添加切片器：

• 切片器字段：SnapshotMonth[Period]

这个切片器只控制需要显示哪些月末快照，不会影响以前月份交易的累计判断。

如果要连续选择一段日期，也可以使用：

SnapshotMonth[MonthEnd]

作为日期范围切片器。

5. 示例计算逻辑

假设有以下交易：

那么各月末的计算结果如下。

2023 年 2 月末

$10 的交易已经创建，但尚未支付：

未支付金额 = $10

2023 年 3 月末

$10 的交易仍未支付，新创建的 $100 交易也没有支付：

未支付金额 = $10 + $100 = $110

2023 年 4 月末

$10 的交易已在 4 月支付，不再计入月末未支付金额。$100 的交易仍未支付：

未支付金额 = $100

注意事项

月末当天完成支付如何处理

按照上面的度量值，月末当天完成支付的交易会被视为“截至月末已经支付”，不会计入当月末的未支付金额。

如果业务规则要求月末当天支付的交易仍计入当月快照，就需要调整时间边界。具体口径应在报表上线前确认。

不要只使用 completed_date = BLANK()

如果只筛选当前 completed_date 为空的交易，就会漏掉那些在历史月末尚未支付、后来才完成支付的记录。

例如，一笔交易在 4 月支付：

• 现在查看时，它已经有 completed_date。
• 但在 2 月末和 3 月末，它仍处于未支付状态。

所以，必须将 completed_date 与每个月的月末边界进行比较，不能只判断它当前是否为空。

保留其他业务筛选器

该度量值只解除 created_date 和 completed_date 上的筛选，不会移除交易类型、客户、地区等其他字段的筛选。这些字段仍然可以用于切片器。

如果模型中还有与交易表关联的正式日期表，并且该日期表通过 created_date 筛选交易，应避免它同时影响月末快照计算。最简单的做法是让 SnapshotMonth 保持独立，并将它用于图表轴和期间切片器。

备注：内容仅供参考。