PowerBI中按合同与发票日期计算应付款金额的求助

数据集 #

需求说明 #

• 明细值列是月度明细数据，月度总额列是当月该合同所有明细值的总和（例如Contract 1在01/01/2023的月度总额为380，即180+200）
• 需要计算每个开票日期对应的应付款金额：针对当前发票日期，累加该合同从上一次开票日期之后到当前发票日期当月及之前的所有月度总额（例如Contract 1在开票日期01/03/2023时，应付款为1380，即01/01/2023的380加上01/02/2023的1000）

原错误DAX代码 #

Amount to be paid = 
  var thisBillDate = table[Invoice Date]
  var prevBillDate = CALCULATE(
    MAX(table[invoice Date]),
    ALLEXCEPT(table, table[Contract]),
   table[Invoice Date] < thisBillDate
  )
  var result = CALCULATE(
    SUM(table[Monthly total]),
    ALLEXCEPT(tale, table[Contract], table[Date]),
    prevBillDate < table[Invoice Date] &&  table[Invoice Date] <= thisBillDate)

  return IF(NOT ISBLANK(table[Invoice Date]), COALESCE(result, 0))

问题排查与修正 #

原代码存在的问题 #

1. 拼写错误：ALLEXCEPT(tale, ...)中的tale应为table（表名拼写错误）
2. 筛选逻辑错误：计算result时错误使用Invoice Date作为筛选条件，实际应基于Date列（月度日期）筛选区间，因为要累加的是对应月度的总额
3. 上下文混淆：prevBillDate未处理首次开票的空值情况，且筛选范围逻辑不清晰

修正后的DAX代码 #

Amount to be paid = 
VAR CurrentContract = 'table'[Contract]
VAR CurrentInvoiceDate = 'table'[Invoice Date]
-- 获取当前合同的上一次开票日期（无则返回空值）
VAR PreviousInvoiceDate = 
    CALCULATE(
        MAX('table'[Invoice Date]),
        ALLEXCEPT('table', 'table'[Contract]),
        'table'[Invoice Date] < CurrentInvoiceDate
    )
-- 提取当前合同的唯一月度日期（去重避免重复计算月度总额）
VAR UniqueMonthlyDates = 
    CALCULATETABLE(
        VALUES('table'[Date]),
        ALLEXCEPT('table', 'table'[Contract])
    )
-- 筛选需要累加的月度：首次开票时包含所有≤当前开票日期的月度；非首次则包含上次开票日期之后到当前开票日期的月度
VAR TargetMonths = 
    FILTER(
        UniqueMonthlyDates,
        (ISBLANK(PreviousInvoiceDate) || 'table'[Date] > PreviousInvoiceDate) && 
        'table'[Date] <= CurrentInvoiceDate
    )
-- 计算目标月度的总额之和
VAR TotalAmount = 
    CALCULATE(
        SUM('table'[Monthly total]),
        ALLEXCEPT('table', 'table'[Contract]),
        'table'[Date] IN TargetMonths
    )
-- 仅在有开票日期时返回结果，无则留空
RETURN
    IF(NOT ISBLANK(CurrentInvoiceDate), COALESCE(TotalAmount, 0), BLANK())

代码逻辑说明 #

1. 先定义当前行的合同和开票日期变量，明确上下文
2. 用ALLEXCEPT保留合同维度，筛选出小于当前开票日期的最大开票日期，处理首次开票的空值场景
3. 提取每个合同的唯一月度日期，避免同一月多行明细导致重复计算月度总额
4. 精准筛选需要累加的月度区间，确保覆盖从上一次开票后到当前开票日的所有月度
5. 基于筛选出的月度，累加对应的月度总额
6. 仅当开票日期非空时返回计算结果，符合数据集的展示逻辑

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