Forum Discussion
Circular Dependency detected
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
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
Hi Anonymous,
Does that make sense? If so, kindly mark the answer as a solution to close the case please.
Regards,
Frank
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.