Circular Dependency detected

[Anonymous]

Here is what I'm trying to accomplish.  We have inventory with different month ages. If it is 0-6 months old, we reserve 10% of total cost, if it's 7-12 months old, we reserve 20% of total cost, and 50% of total cost above 12 months age.

I can get this total easily with the calculated columns below.  My issue is I want to predict what our reserves will total next month, if we don't sell anything.  So I create another calculated column that adds 1 month to the current months aged calculated column and group that new column into my month groups........long story short, I get a circular dependency error.  After research, it has to do with multiple calculated columns in the same table.  I tried to create a measure "datediff" but I have to use Min, Max, or Sum and none of these work with my table due to different issues.  How can I accomplish this?

I have a table:

partnumber, qty, cost, receive date, report run date

Measure:

Total Cost = SUMX(Table1, Table1[Qty]*Table1[Cost]

I created a calculated column: 

Months Aged =
DATEDIFF('VSE Inventory'[Receive Date], 'VSE Inventory'[Run Date], MONTH)

I grouped the "Months Aged" column in 0-6 Months, 7-12 Months, Over 12 Months

Created a calculated column:

E&O Reserves =

IF(Table1[Months Aged (groups)] = "0-6 Months", [Total Cost] *.1,

IF(Table1[Months Aged (groups)] = "7-12 Months", [Total Cost] *.2,,

IF(Table1[Months Aged (groups)] = "Over 12 Months", [Total Cost] *.5,0)))

This all works great.  I want to predict what our E&O Reserves are going to be next month if we don't sell a single item.

I created a calculated column:

Months Aged +1 =
'VSE Inventory'[Months Aged] + 1

I grouped the "Months Aged +1" column in 0-6 Months, 7-12 Months, Over 12 Months

Created a calculated column:

E&O Reserves +1 =

IF(Table1[Months Aged (groups) +1] = "0-6 Months", [Total Cost] *.1,

IF(Table1[Months Aged (groups) +1] = "7-12 Months", [Total Cost] *.2,,

IF(Table1[Months Aged (groups) +1] = "Over 12 Months", [Total Cost] *.5,0)))

I get a circular error with the E&O Reserves +1 column.

I can fix all of this by just adding a column to my table through the query editor but then I would have to write the datediff function in m language because I need it to convert to two dates down to month difference. I can't find how to use datediff in the m language.

[v-frfei-msft]

Hi @Anonymous,

Based on my test, here we needn't to create so many calculated columns. We can create measures insdead of them. Please refer to the formulas as below.

Months Aged = DATEDIFF(MAX(Table1[DATE1]),MAX(Table1[DATE2]),MONTH)

Months Aged (groups)m = IF(0<=[Months Aged] && [Months Aged]<=6,"0-6 Months",IF(7<=[Months Aged] && [Months Aged]<=12, "7-12 Months","Over 12 Months"))

E&O Reserves1 = 
IF([Months Aged (groups)m] = "0-6 Months", [Total Cost] *0.1,
IF([Months Aged (groups)m] = "7-12 Months", [Total Cost] *0.2,
IF([Months Aged (groups)m] = "Over 12 Months", [Total Cost] *0.5,0)))

Months Aged +1 = [Months Aged]+1

Months Aged (groups)+1 = IF(0<=[Months Aged +1] && [Months Aged +1]<=6,"0-6 Months",IF(7<=[Months Aged +1] && [Months Aged +1]<=12, "7-12 Months","Over 12 Months"))

E&O Reserves +1 = 
IF([Months Aged (groups)+1] = "0-6 Months", [Total Cost] *0.1,
IF([Months Aged (groups)+1] = "7-12 Months", [Total Cost] *0.2,
IF([Months Aged (groups)+1] = "Over 12 Months", [Total Cost] *0.5,0)))

Here is the result for your reference,

For more details, please check the pbix as attached.

https://www.dropbox.com/s/15aubwuj0unbxl2/Dependency%20detected.pbix?dl=0

Regards,

Frank

[Anonymous]

I believe my issue is going to be the MAX in the "Months Ages" measure.  I have the same part in inventory with 50 lines.  Each line is the same part number but with different receive dates.  A few of the lines could be 8 months old, were some lines will be days old.  When I use the max, it will group them all with the "Max" date that exists with that part, instead of their actual individual receive dates.

I hope this makes since on how I'm explaining it.

[v-frfei-msft]

Hi @Anonymous,

Could you please share your pbix to me?

Regards,

Frank

[Anonymous]

Reserves.pbix

Not sure how to share the file.  I hope this works.

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

I have updated your pbix, Please find the file attached. And I create measures to replace the calculated columns.

Months Aged = 
 DATEDIFF(MAX('VSE Inventory'[Rec Date]), MAX('VSE Inventory'[Report Run Date]), MONTH)

Age Groups = if('VSE Inventory'[Months Aged]<7,"0 to 6 Months",if(and('VSE Inventory'[Months Aged]<10,'VSE Inventory'[Months Aged]>6),"7 to 9 Months",if(and('VSE Inventory'[Months Aged]<13,'VSE Inventory'[Months Aged]>9),"10 to 12 Months",if(and('VSE Inventory'[Months Aged]<19,'VSE Inventory'[Months Aged]>12),"13 to 18 Months",if(and('VSE Inventory'[Months Aged]<25,'VSE Inventory'[Months Aged]>18),"19 to 24 Months",if(and('VSE Inventory'[Months Aged]<37,'VSE Inventory'[Months Aged]>24),"25 to 36 Months",if(and('VSE Inventory'[Months Aged]<49,'VSE Inventory'[Months Aged]>36),"37 to 48 Months",if(and('VSE Inventory'[Months Aged]<61,'VSE Inventory'[Months Aged]>48),"49 to 60 Months","Over 60 Months"))))))))

E&O Reserves = 
 IF('VSE Inventory'[Age Groups] = "37 to 48 Months", [Total Cost] *0.1,
 IF('VSE Inventory'[Age Groups] = "49 to 60 Months", [Total Cost] *0.2,
 IF('VSE Inventory'[Age Groups] = "Over 60 Months", [Total Cost] *0.5,0)))

Regards,

Frank

[v-frfei-msft]

Hi @Anonymous,

Does that make sense? If so, kindly mark the answer as a solution to close the case please.

Regards,

Frank

[Anonymous]

Sorry for the delay and thanks for your help on this.  My main issue is this.  I want to display what our current reserve is as of today.(It looks like your code is working correctly and now that piece is working great.)  Now, I want to predict what the reserve will be next month, then two months from now, three, four, five and six months from today.  

With each passing month, we will have inventory moving from the 20% reserve "49 to 60" month group to the 50% reserve "Over 60" month group, so naturally, as the months go by, and if nothing is sold, the "Reserve" will grow.  The result will look like the image I attached.

In the example in the image, I want to be able to go to the business leader, tell them that their current inventory reserve is $103,702,, but if they don't sell any of the aged inventory that is currently in these three groups within the next six months, the reserve will grow to $153,454, which will be a $49,751 increase over that time span.

One more example.

I have 1 unit with a cost of $1.00.  Today, it is 59 months old.  It currently falls into the "49 to 60" month old group, which carries a 20% reserve.  I am reserving $0.20 today for that unit.

I can predict that if I don't sell it by next month, it will then be 60 months old. It will still fall in the "49 to 60" month old group, and my reserve is unchanged at $0,20. 

However, today I can also predict that if I don't sell it two months from now, it will then be 61 months old, which will move it from the 20% group to the 50% reserve group "Over 60" months old.  This means my reserve will increase from $0.20 to $0.50.