Help with Circular Reference Error

[Anonymous]

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(Tier2 threshold)  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 month New 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]

[amitchandak · Accepted solution]

@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]

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]

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