如何在DAX中引用前值计算理想速率且避免循环依赖

我想要基于之前理想速率的总和来计算理想速率，规则如下：

• 第一行的理想速率始终为[New Orders] / [Hours Remaining]，例如本示例中1322/8=165.25。
• 从第2行到第n行，理想速率应为（[New Orders]的累计总和 - 之前所有行[Ideal Rates]的累计总和）/ [Hours Remaining]，示例：
  • 第2行：((1322 + 0) - 165.25) / 7 = 165.25
  • 第3行：((1322 + 0 + 38) - (165.25 + 165.25)) / 6
  • 第4行：((1322 + 0 + 38 + 5) - (165.25 + 165.25 + 171.58)) / 5

我难以找到正确引用前一行速率来调整累计订单的方法。

现有DAX代码 #

New Orders = DistinctCount(Orders[Order_Number])
Hours Remaining = DATEDIFF(MAX('Time'[Time]),CALCULATE(MAX('Time'[Time]),REMOVEFILTERS('Time'[Time])),HOUR)
Ideal Rate = 

VAR Time = CALCULATE(MIN('Time'[Time]),ALLSELECTED('Time'))
VAR CurrentRowRate = DIVIDE([New Orders],[Hours Remaining],0)
VAR CumOrders = 
    CALCULATE(
        [New Orders],
        WINDOW(0,ABS,0,REL,ALLSELECTED('Time'[Time]))
    )
VAR CumPriorRowRates = 
    CALCULATE(
        CumOrders - CurrentRowRate,
        WINDOW(0,ABS,-1,REL,ALLSELECTED('Time'[Time]))
    )
VAR AdjustedCurrentRowRate = 
    CumPriorRowRates  / [Hours Remaining]
RETURN

IF(MAX('Time'[Time]) = Time,
   CurrentRowRate,
   AdjustedCurrentRowRate)

R实现代码（已验证逻辑） #

我已通过R可视化工具迭代引用之前的累计总和实现了该逻辑，但理想情况下我希望用DAX实现。

for (i in 1:nrow(Orders_By_Hour)) {
  
  Orders_By_Hour$Ideal_Rate[i] <- ifelse(Orders_By_Hour$Time[i] == min(Orders_By_Hour$Time),
      Orders_By_Hour$Cum_Orders[i]/Orders_By_Hour$Hours_Remaining[i],
      (Orders_By_Hour$ending_sum[i-1]+Orders_By_Hour$New_Orders[i])/Orders_By_Hour$Hours_Remaining[i])
  
  Orders_By_Hour$ending_sum[i] <- ifelse(Orders_By_Hour$Time[i] == min(Orders_By_Hour$Time),
                              Orders_By_Hour$Cum_Orders[i] - Orders_By_Hour$Ideal_Rate[i],
                              Orders_By_Hour$ending_sum[i-1]+Orders_By_Hour$New_Orders[i] - Orders_By_Hour$Ideal_Rate[i])
  
}

参考资料 #

参考的Stack Overflow文章：

• Bring previous day value as starting point for next day in a measure within Power BI
• Self-reference a column in DAX or Power Query

参考的YouTube视频：

• SOLVE Circular Dependency issues in Recursive Inventory problems | MAGIC of WINDOW() + OFFSET()
• Microsoft Hates Greg - Previous Value ("Recursion") in DAX

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