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

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.

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
8 years ago

Hi Anonymous,

 

Could you please share your pbix to me?

 

Regards,

Frank

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Reserves.pbix

     

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

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

        Hi Anonymous,

         

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

         

        Regards,

        Frank