基于日期计算期初/期末余额：Power BI循环依赖问题求助

核心计算逻辑 #

• 期初余额（OpeningBalance）：首日初始金额取自其他表（已按年份创建对应度量值）；其余日期的期初余额为前一日的期末余额。
• 销售额（Sales）：存储销售金额的列。
• 期末余额（EndingBalance）：月度期末余额 = 期初余额 + 月度销售额。

计算示例 #

首日：

OpeningBalance = 10
Sales = 15
EndingBalance（OpeningBalance+Sales）= 25

次日：

OpeningBalance = 25（前一行的EndingBalance）
Sales = 20
EndingBalance（前一行的EndingBalance+Sales）= 45

按此逻辑循环至最后一个可用日期。

当前代码与问题 #

尝试将期末余额度量值中的[OB Initial]替换为[Opening Balance Adjusted]时，触发循环依赖错误——原因是两个度量值互相引用，形成闭环。

现有期初余额度量值 #

Opening Balance Adjusted =
VAR _SelectedMonth = SELECTEDVALUE(Activitylist[FX_MonthNumber])
VAR _SelectedYear = SELECTEDVALUE(Activitylist[FX_Year])

RETURN
IF(
MAX(Activitylist[IsFirstMonth]) = 1 && _SelectedYear = SELECTEDVALUE(Activitylist[FX_Year]),
[OB Initial Column Measure],  // 首月使用OB2024度量值

CALCULATE(
    [Closing Balance Measure],

    FILTER(
        ALL('Activitylist'),
        'Activitylist'[FX_MonthNumber] = _SelectedMonth - 1 &&
        'Activitylist'[FX_Year] = _SelectedYear &&
        'Activitylist'[LoB] = SELECTEDVALUE(Activitylist[LoB])
    )
)

)

现有期末余额度量值 #

Closing Balance Measure =
VAR _SelectedMonth = SELECTEDVALUE(Activitylist[FX_MonthNumber])
VAR _SelectedYear = SELECTEDVALUE(Activitylist[FX_Year])
VAR _OBInitial = SELECTEDVALUE(Activitylist[OBInitialColumn])
VAR _OBdjusted = SELECTEDVALUE(Activitylist[OBAdjustedColumn])

RETURN
IF(
MIN(Activitylist[IsFirstMonth]) = 1 && SELECTEDVALUE(Activitylist[FX_Year]) = _SelectedYear,
_OBInitial + [Monthly Variation],  // 首月计算逻辑
CALCULATE(
ROUND( [OB Initial] + [Monthly Variation],0)
// 问题点：此处需替换为[Opening Balance Adjusted]，但会触发循环依赖错误
)

循环依赖解决方案 #

重构度量值逻辑，避免互相引用，改用初始余额+累计销售额的方式直接计算：

修正后的期初余额度量值 #

Opening Balance Adjusted =
VAR CurrentMonth = SELECTEDVALUE(Activitylist[FX_MonthNumber])
VAR CurrentYear = SELECTEDVALUE(Activitylist[FX_Year])
VAR CurrentLoB = SELECTEDVALUE(Activitylist[LoB])
VAR IsFirstMonth = MAX(Activitylist[IsFirstMonth])

RETURN
IF(
    IsFirstMonth = 1 && CurrentYear = SELECTEDVALUE(Activitylist[FX_Year]),
    [OB Initial Column Measure],
    // 计算截至上月的累计销售额，加上初始余额得到期初
    [OB Initial Column Measure] + CALCULATE(
        [Monthly Variation],
        FILTER(ALL(Activitylist),
            Activitylist[FX_Year] = CurrentYear &&
            Activitylist[FX_MonthNumber] < CurrentMonth &&
            Activitylist[LoB] = CurrentLoB
        )
    )
)

修正后的期末余额度量值 #

Closing Balance Measure =
VAR CurrentMonth = SELECTEDVALUE(Activitylist[FX_MonthNumber])
VAR CurrentYear = SELECTEDVALUE(Activitylist[FX_Year])
VAR CurrentLoB = SELECTEDVALUE(Activitylist[LoB])
VAR IsFirstMonth = MIN(Activitylist[IsFirstMonth])

RETURN
IF(
    IsFirstMonth = 1 && CurrentYear = SELECTEDVALUE(Activitylist[FX_Year]),
    [OB Initial Column Measure] + [Monthly Variation],
    // 计算截至当前月的累计销售额，加上初始余额得到期末
    [OB Initial Column Measure] + CALCULATE(
        [Monthly Variation],
        FILTER(ALL(Activitylist),
            Activitylist[FX_Year] = CurrentYear &&
            Activitylist[FX_MonthNumber] <= CurrentMonth &&
            Activitylist[LoB] = CurrentLoB
        )
    )
)

替代简化写法（基于日期列） #

若数据表包含完整日期列，可直接用累计逻辑实现，更简洁：

Closing Balance Measure =
VAR CurrentDate = MAX(Activitylist[Date])
VAR InitialOB = [OB Initial Column Measure]
VAR TotalSalesToDate = CALCULATE(
    [Monthly Variation],
    FILTER(ALL(Activitylist),
        Activitylist[Date] <= CurrentDate &&
        Activitylist[LoB] = SELECTEDVALUE(Activitylist[LoB])
    )
)
RETURN InitialOB + TotalSalesToDate

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