Power BI中避免循环依赖创建日起始目标与日结束结果列

需求表格 #

需要在Power BI中构建如下表格：

字段规则 #

• Date：取自timeslips表的日期记录
• Daily Billed hrs：取自timeslips表的每日工时记录
• Daily Start Goal：
  • 前一日结束值为空时，取Avg/Day计算值
  • 否则，用Avg/Day减去前一日的结束值（原IFS逻辑可简化为此）
• Daily End Outcome：直接用当日工时减去当日目标值（原IFS逻辑等价于这个减法）

遇到的问题 #

尝试以下DAX度量值时触发循环依赖错误，原因是Goal和End两个度量值互相引用，形成了闭环：

错误的Goal度量值：

Goal =  
VAR PreviousEnd = CALCULATE ([End],PREVIOUSDAY ( timeslips[timeslip_date] )) 
VAR AvgPerDay = [AVG/Day]  -- 替换为实际的Avg/Day度量值
RETURN 
        IF(ISBLANK(PreviousEnd), AvgPerDay, AvgPerDay - PreviousEnd)

错误的End度量值：

End =
VAR CurrentGoal = [Goal]
VAR CurrentHours = SUM(Timeslips[Daily Billed hrs])
RETURN
IF(
CurrentGoal > CurrentHours,
CurrentGoal - CurrentHours,
CurrentHours - CurrentGoal
)

解决方案 #

方案一：使用计算列（适用于每日唯一日期的场景） #

先添加Daily Start Goal计算列：

Daily Start Goal = 
VAR AvgPerDay = CALCULATE([AVG/Day], ALL(timeslips)) -- 获取全局Avg/Day值
VAR PrevDate = PREVIOUSDAY(timeslips[timeslip_date])
VAR PrevEnd = CALCULATE(MAX(timeslips[Daily End Outcome]), timeslips[timeslip_date] = PrevDate)
RETURN
IF(ISBLANK(PrevEnd), AvgPerDay, AvgPerDay - PrevEnd)

再添加Daily End Outcome计算列：

Daily End Outcome = 
timeslips[Daily Billed hrs] - timeslips[Daily Start Goal]

方案二：使用度量值（配合日期表，推荐Power BI最佳实践） #

先创建一个独立的日期表（与timeslips表建立日期关联），然后创建以下度量值：

Daily End Outcome (Measure)

Daily End Outcome (Measure) = 
VAR CurrentDate = MAX('Date'[Date])
VAR AvgPerDay = [AVG/Day]
-- 筛选出当前日期及之前的所有日期
VAR DateRange = 
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date] <= CurrentDate
    )
-- 迭代计算每日的目标和结果
VAR DailyCalculations = 
    ADDCOLUMNS(
        DateRange,
        "@DailyHrs", CALCULATE(SUM(timeslips[Daily Billed hrs])),
        "@StartGoal", 
            VAR PrevDay = PREVIOUSDAY([Date])
            VAR PrevEndResult = MAXX(FILTER(DailyCalculations, [Date] = PrevDay), [@EndOutcome])
            RETURN IF(ISBLANK(PrevEndResult), AvgPerDay, AvgPerDay - PrevEndResult),
        "@EndOutcome", [@DailyHrs] - [@StartGoal]
    )
-- 返回当前日期的结果
RETURN MAXX(FILTER(DailyCalculations, [Date] = CurrentDate), [@EndOutcome])

Daily Start Goal (Measure)

Daily Start Goal (Measure) = 
VAR CurrentDate = MAX('Date'[Date])
VAR AvgPerDay = [AVG/Day]
VAR PrevDay = PREVIOUSDAY(CurrentDate)
VAR PrevEnd = CALCULATE([Daily End Outcome (Measure)], 'Date'[Date] = PrevDay)
RETURN IF(ISBLANK(PrevEnd), AvgPerDay, AvgPerDay - PrevEnd)

这两种方案都能打破循环依赖，实现需求中的计算逻辑。

内容的提问来源于stack exchange，提问作者Juan Pablo Muriel