Search code examples
powerbidax

How to leverage previous value in dax without yielding a circular dependency?

enter image description here Hi there,

I'm trying to calculate the ideal rate based upon the SUM of the previous ideal rates.

  • The first row will always just be the [New Orders] / [Hours Remaining]. So, in this example, 1,322/8 = 165.25.
  • However, from row 2:n, I want the ideal rate to be the (Cumulative SUM of [New Orders] - Cumulative SUM of the prior [Ideal Rates]) / [Hours Remaining]. So to use three examples:
    • Row 2: ((1,322 + 0) - 165.25) / 7 = 165.25
    • Row 3: ((1,322 + 0 + 38) - (165.25 + 165.25)) / 6
    • Row 4: ((1,322 + 0 + 38 + 5) - (165.25 + 165.25 + 171.58)) / 5

I'm struggling to figure out out to reference the prior row rates to adjust the cumulative orders.

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)

I've been able to implement this in an R Visual by iteratively referencing the prior cumulative sum. However, ideally, I wanted to do this in 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 Articles Referenced:

Youtube Videos Referenced:

Solution

  • We can simplify your formula to simply:

    for this row and all previous rows, sum (orders divided by hours, for each row)

    Then you can use this measure:

    Ideal Rate = 
  SUMX(
    WINDOW(0,ABS,0,REL,ALLSELECTED('Time'[Time])),
    DIVIDE([New Orders], [Hours Remaining])
  )

    Example result