How to map/allocate product cost over time based on flat data?
We are doing a project around Margin by SKU and I am having trouble mapping/allocating product cost to a dynamic Time dimension.
Attached is what our flat cost data looks like. DL is the location, 14190 is the item/SKU, and the 11/12/W1 are different cost types (variable, variable + labor, etc). We've got the cost and then the Effective date. As you can see, this item has had 3 cost changes, 1/21, 2/24, and 4/2/21. The IEEDT column is sort of an expiration date, but really the 1/21 costs expire on 2/24 when the cost is updated. Although these are updated on specific days, we are doing analysis on month/quarterly basis. I am fine with the 1/21 cost being the cost for January, the 2/24 cost being the cost for Feb/March, and the 4/2 cost being April-forward.
I am having trouble figuring out how to assign these costs to each product given time. For example, the margin and costs for this product should be different when looking at January vs April.
I've thought about having a module that is dimensionalized by Time, SKU, and cost type to house the costs by sku over time, but not sure how I would populate this module. I've attached a snippet of ideally what that looks like, just not sure how to map the costs to the time periods.