Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Circular Dependency detected

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 ...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    8 years ago

    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's avatar
v-frfei-msft
Community Support
8 years ago

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

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.