Forum Discussion
Standard Cost and Cycle Counts
I'm trying to figure out a way to calculate the value of the inventory that has gone through the cycle count process. There are two main tables:
CycleCountFact: Company, CycleID,ItemID, OnhandQty,CountedQty, ApprovedDate
StandardCost: Company, ItemID, StandardCost, ActivationDate
I have two issues:
1. I'm not sure what the best way to relate the tables is. I've created a concatenation of the Company and ItemID on both tables but it still leads to a Many-to-Many relationship since the StandardCost of a given ItemID can multiple ActivationDates. Any ideas on the best way to relate the two?
2. How would I call the StandardCost with the LATEST ActivationDate BEFORE the ApprovalDate of my Cycle count?
Let's say ItemX has two Costs with different ActivationDates.
X $100 6/25/2019
X $400 6/1/2019
If my Cycle count has an ApprovedDate of 6/7/2019, I expect it to have a cost of $400.
Please help!
Hi Anonymous
I’ve created 2 tables as your requested without adding any relationship as below:
Add following 2 measures:
Latest day before Approving = CALCULATE(MAX(StandardCost[ActivationDate]),FILTER(ALL('StandardCost'),StandardCost[ActivationDate]<=MAX('CycleCountFact'[ApprovedDate])&&StandardCost[ItemID]=MAX(CycleCountFact[ItemID])&&StandardCost[Company]=MAX(CycleCountFact[ Company]))) Standardcost = CALCULATE(MAX(StandardCost[StandardCost]),FILTER(StandardCost,[Latest day before Approving]=[ActivationDate]))Then we can get the corresponding standardcost which belongs to the most latest Activation date before Approved date.
Best regards,
Dina Ye
2 Replies
- v-diye-msft
Community Support
Hi Anonymous
I’ve created 2 tables as your requested without adding any relationship as below:
Add following 2 measures:
Latest day before Approving = CALCULATE(MAX(StandardCost[ActivationDate]),FILTER(ALL('StandardCost'),StandardCost[ActivationDate]<=MAX('CycleCountFact'[ApprovedDate])&&StandardCost[ItemID]=MAX(CycleCountFact[ItemID])&&StandardCost[Company]=MAX(CycleCountFact[ Company]))) Standardcost = CALCULATE(MAX(StandardCost[StandardCost]),FILTER(StandardCost,[Latest day before Approving]=[ActivationDate]))Then we can get the corresponding standardcost which belongs to the most latest Activation date before Approved date.
Best regards,
Dina Ye