Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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'...
  • amitchandak's avatar
    amitchandak
    5 years ago

    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)

amitchandak's avatar
amitchandak
Super User
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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))