如何按不同起始日期将12个月总费用分摊至各客户月度费用？

一、原代码报错原因 #

1. RELATED函数失效：CROSSJOIN生成的行上下文未与Merged - Service Period表建立直接关联，RELATED无法获取对应字段值。
2. 日期类型不匹配：StartMonthYear是YYYY-MM格式的字符串，DATEADD函数仅支持日期类型，无法直接运算。
3. 逻辑判断模糊：COUNTROWS('Service Periods')未添加筛选条件，无法准确判断服务期有效性。

二、修正后的DAX计算表代码 #

以下代码会生成符合要求的宽表，首列为HouseholdID，后续为YYYYMM格式的月度列，自动处理续约费用更新：

ExtrapolatedTable = 
// 第一步：整理服务期核心信息，转换为日期格式的起止月度
VAR AllServicePeriods = 
    SELECTCOLUMNS(
        'Merged - Service Period',
        "HouseholdID", [finpal_householdid],
        "TotalFee", [Fees Exc. GST],
        "StartMonth", DATE(YEAR([Startdate]), MONTH([Startdate]), 1), // 服务期起始月1号
        "EndMonth", DATEADD(DATE(YEAR([Startdate]), MONTH([Startdate]), 1), 11, MONTH) // 12个月周期的最后一个月
    )
// 第二步：生成每个服务期对应的12个月度记录
VAR PeriodMonthCross = 
    GENERATE(
        AllServicePeriods,
        DATESBETWEEN('Date Table'[Date], [StartMonth], [EndMonth])
    )
// 第三步：计算月度分摊费用并格式化年月
VAR MonthlyFees = 
    ADDCOLUMNS(
        PeriodMonthCross,
        "MonthlyFee", DIVIDE([TotalFee], 12),
        "YearMonth", FORMAT([Date], "YYYYMM")
    )
// 第四步：生成所有家庭+所有月度的全量组合，填充未覆盖月度的0值
VAR AllHouseholdMonths = 
    CROSSJOIN(
        VALUES('Merged - Service Period'[finpal_householdid]),
        SELECTCOLUMNS('Date Table', "YearMonth", FORMAT([Date], "YYYYMM"))
    )
// 第五步：合并分摊费用，无服务期的月度填0
RETURN
    ADDCOLUMNS(
        AllHouseholdMonths,
        "MonthlyFee",
        COALESCE(
            CALCULATE(
                SUM(MonthlyFees[MonthlyFee]),
                FILTER(
                    MonthlyFees,
                    MonthlyFees[HouseholdID] = [finpal_householdid] && MonthlyFees[YearMonth] = [YearMonth]
                )
            ),
            0
        )
    )

三、矩阵可视化方案（无需生成物理表，更高效） #

如果不需要生成物理表，直接用矩阵可视化可以快速实现需求，步骤如下：

1. 创建度量值 #

Monthly Fee Allocation = 
VAR CurrentMonth = MAX('Date Table'[Date])
VAR CurrentHousehold = SELECTEDVALUE('Merged - Service Period'[finpal_householdid])
// 筛选当前家庭在当前月度有效的服务期（含续约的新服务期）
VAR ActivePeriods = 
    FILTER(
        'Merged - Service Period',
        [finpal_householdid] = CurrentHousehold &&
        CurrentMonth >= DATE(YEAR([Startdate]), MONTH([Startdate]), 1) &&
        CurrentMonth <= DATEADD(DATE(YEAR([Startdate]), MONTH([Startdate]), 1), 11, MONTH)
    )
// 计算当前月度的总分摊费用（多个重叠服务期会累加，若需取最新服务期可替换为MAXX）
VAR TotalMonthly = SUMX(ActivePeriods, DIVIDE([Fees Exc. GST], 12))
RETURN COALESCE(TotalMonthly, 0)

2. 配置矩阵可视化 #

• 行字段：Merged - Service Period[finpal_householdid]（或关联的客户表HouseholdID）
• 列字段：Date Table[YearMonth]（设置格式为YYYYMM）
• 值字段：上述创建的Monthly Fee Allocation度量值

注意事项 #

• 确保Date Table是包含所有需要统计月度的完整日期表，且Date字段为日期类型。
• 若服务期存在重叠，矩阵方案默认累加费用；若需优先使用最新续约的费用，可将SUMX替换为MAXX，并添加按Startdate排序的逻辑。

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