cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Help with Circular Reference Error

Hello Power BI Gurus,

I am stuck with an issue where I have created a few measures and one of the measure is erroring out because of circular reference. It have tried many alternatives but haven't been successful. It might be pertty trivial issue and I may have been overthinking.

Requirement : I have a data set about agents completing cases everymonth and getting paid in Tiers depending upon the cases they complete. If they don't reach threshold for a Tier , the cases get carried to next month and evaluated with next month's cases .Tiers defined are 40,80 and 120 , so if an agent completes 37 cases,all of his cases get processed next month and if his completes 45 , he gets paid for 40 and 5 of his cases move to next month & so on.

Here is the data set and calculations I have iused to implement the logic where 4th measure errors out with circular reference. Any ideas on how I can resolve this as I have tried quite a few things but nothing seemed to have helped resolving this.Thanks for your help in advance

 Table columns Table columns Table columns Table columns Measures Measures Measures Measures Measures Agent Month Cases solved Carry forward to next month Pending from Last month Total cases this month Carry forward from Previous Month Tier AB Jan 20 20 7 (20+7) =27 7 No Payment AB Feb 30 30 20 27+30=57 and not 20+30 as 27 from previous month is below threshold and hence carried forward 27 Tier1 AB Mar 40 0 30 40+17 from prev month (As 57 crossed tier 1 threshold hence remaining 17 would flow through to this month 17 Tier1 AB Apr 60 20 0 60+17 =77 to be processed which falls short of 80(Tier2threshold)  and hence 37 would carry forward 37 Tier1 AA Jan 30 30 20 30+20=50 ,40 to be processed and 10 carry forward to next monthNew agent counter restarts from first month of service for agent) 10 Tier1 AA Feb 80 0 30 90 10 Tier2 AA Mar 90 10 0 90+10 20 Tier2 AA Apr 46 6 10 66 26 Tier1

Below are the measure calculations :

1)
PendingLastmonth = CALCULATE(sum(Monthlypayments[Carry forward to next month]),PREVIOUSMONTH(Calendar_dim[Date].[Date]))

2) Carry Forward from previous month = CALCULATE([Carried to next month],PREVIOUSMONTH(Calendar_dim[Date].[Date]))

3)
Carried to next month = if([totalcase]<40, [Totalcase], (if([Totalcase]>=40
&& [Totalcase]<80, [Totalcase]-40,(if(([Totalcase])>=80 && [Totalcase]<120,
[Totalcase]-80,(if([Totalcase]>=120, [Totalcase]-120,0)))))))
4)
Totalcase = sum(Monthlypayments[Total case])+[Carried from previous month]

1 ACCEPTED SOLUTION
Super User

@Anonymous , this sequence of measure is not correct,

Create a cumulative measure and try like

Cumm cases last month = CALCULATE(sum(Monthlypayments[Total case]),filter(allselected(date),date[date] <=maxx(date,dateadd(date[date]),-1,year)))
cases this month = sum(Monthlypayments[Total case])

paid this month = Quotient(mod([Cumm cases last month],40) + [Cumm cases this month], 40)

3 REPLIES 3
Super User

@Anonymous , this sequence of measure is not correct,

Create a cumulative measure and try like

Cumm cases last month = CALCULATE(sum(Monthlypayments[Total case]),filter(allselected(date),date[date] <=maxx(date,dateadd(date[date]),-1,year)))
cases this month = sum(Monthlypayments[Total case])

paid this month = Quotient(mod([Cumm cases last month],40) + [Cumm cases this month], 40)

Anonymous
Not applicable

Hi Amit, Thanks for your previous solution. There is one more issue on top of the above, that I got stuck with and I am not able to get it working.  If an employee's total is more than 120 cases then we need to carry forward the entire amount forward rather than a mod of 40, For eg, if the total is 165 then we should carry forward 45 and not 5 . I have tried using If condition to divide by 40 when <120 and divide by 120 and use that to associate it to the next month's row .

Carry forward t o next month does work well but when I try to use in the next row using previous month or date add it doesn't quite me the correct results

CarryforwardtoNextmonth = If((Sum(Monthlypayments[Total Specialities])+[CarryForward]) >120,mod(Sum(Monthlypayments[Total Specialities])+[CarryForward],120),mod(Sum(Monthlypayments[Total Specialities])+[CarryForward],40)).

Casesfrompreviousmonth=calculate(carry forward to next month, previousmonth(calendar.date))

Anonymous
Not applicable

That's Brilliant Amit! Thanks a lot for this quick solution! I was struggling to get the Cumulative sum working as I use using Max in the data comparison rather than MAxx function.  This really helped me get through the blocker. Thanks again for your help

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.