Forum Discussion

pradnyagophane's avatar
pradnyagophane
Regular Visitor
2 years ago

Need help to resolve circular reference error

I have 4 calculation based on columns index, Allowance of hours (default is 10), Hours used, Credit Hours and Remaining.

Remaining is calculated as : Allowance of hours - Hours used + Credit Hours

From Index 2 onwards, Allowance of hours = Allowance of hours (default is 10) + Remaining of Previous row only in case if it is positive number.

Here, when I try to place calculations for remaining = Allowance of hours - Hours used + Credit Hours, I get circular reference error.

Following are the DAX I used.

PrevRemaining =
VAR StartingPrevBalance = MIN(ProjectServiceAgreements[prevbalance])
 
VAR Previousrow =
CALCULATE (
   [Remaining (or overage)],
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Index]
            = CALCULATE (
                MAX ( 'Calendar'[Index] ),
                FILTER ( ALL ( 'Calendar' ), 'Calendar'[Index] < SELECTEDVALUE ( 'Calendar'[Index] ) )
            )
    )
)
VAR selectedIdx = SELECTEDVALUE ('Calendar'[Index])
RETURN IF(selectedIdx=1,StartingPrevBalance+[Default Allowance of Hours], Previousrow)
  
Allowance of Hours = IF([PrevRemaining]>0, [PrevRemaining]+[Default Allowance of Hours],[Default Allowance of Hours])
 
Remaining (or overage) = [Default Allowance of Hours]-[Hours Used]+[Credit Hours]
Here in this calculation if I place , 
Remaining (or overage) = [Allowance of Hours]-[Hours Used]+[Credit Hours] then I get A circular dependancy issue. 
See the index 5, remaining calculations are wrong as I am not able to refere [Allowance of Hours] due to circu
 
Please help.
Data Modeling

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi pradnyagophane ,

     

    The problem is with [Allowance of Hours], how you defined it, please check that its logic applies the values that still need to be calculated.

     

    Best regards,
    Community Support Team_ Scott Chang

    • pradnyagophane's avatar
      pradnyagophane
      Regular Visitor
      2 years ago

      Here I am placing the data and expected result (remianing column)

      IndexMonthAllowance of HoursHours UsedCredit HoursRemaining (or overage)
      1May-231013.250-3.25
      2Jun-231032.750.75-22
      3Jul-231035.756-19.75
      4Aug-23107.250.53.25
      5Sep-2313.257.251.57.5
      6Oct-2317.5363.5-15
      7Nov-231067.2545.5-11.75
      8Dec-231014.55.250.75
      9Jan-2410.750010.75
      10Feb-2420.7534.510-3.75
      11Mar-24101.2508.75
      12Apr-2418.757.75011
      13May-242110.5010.5
      14Jun-2420.50020.5
      15Jul-2430.50030.5

       

      Allowance of hours calculation:

       

      Remaining Calculation:

       

      Result is remaining.

      Remaining = Allowance of Hours (default is 10) + Remaing of previous month if the number is positive -Hours used + Credit hours.

       

      Formula needed to apply in Power BI is to get correct remaining. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi pradnyagophane ,

         

        This seems unachievable. Allowance of hours is used when calculating Remaining.To calculate Allowance of hours, you need to determine the positive or negative of Remaining.

         

        Best regards,
        Community Support Team_ Scott Chang

  • pradnyagophane's avatar
    pradnyagophane
    Regular Visitor
    2 years ago

    Yes, [Allowance of Hours] need to be calculated. Default is 10 hours. But if the previous month remaining is positive value then it becomes 10 + previous month remaining

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi pradnyagophane ,

     

    Please share your sample data and expected results.

     

    Best regards,
    Community Support Team_ Scott Chang