Need help to resolve circular reference error

[pradnyagophane]

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.

[Anonymous]

Hi @pradnyagophane ,

Please share your sample data and expected results.

Best regards,
Community Support Team_ Scott Chang

[pradnyagophane]

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]

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]

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

Index	Month	Allowance of Hours	Hours Used	Credit Hours	Remaining (or overage)
1	May-23	10	13.25	0	-3.25
2	Jun-23	10	32.75	0.75	-22
3	Jul-23	10	35.75	6	-19.75
4	Aug-23	10	7.25	0.5	3.25
5	Sep-23	13.25	7.25	1.5	7.5
6	Oct-23	17.5	36	3.5	-15
7	Nov-23	10	67.25	45.5	-11.75
8	Dec-23	10	14.5	5.25	0.75
9	Jan-24	10.75	0	0	10.75
10	Feb-24	20.75	34.5	10	-3.75
11	Mar-24	10	1.25	0	8.75
12	Apr-24	18.75	7.75	0	11
13	May-24	21	10.5	0	10.5
14	Jun-24	20.5	0	0	20.5
15	Jul-24	30.5	0	0	30.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]

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