I have two data streams, one showing cost price history, the other showing retail price history.
I want to create a single table that shows at a given point in time (the last day of a fiscal period in this case) what was the cost and retail price of a given product.
I'm not sure of the best way to do this, would it be to create an iterative macro that checks whether Fiscal Period Data. Last Date >= Start Date and <= EndDate?
There are hundreds of thousands of price change records and over 60k SKUs so need something relatively efficient performance wise.
Cost Price Data
Retail Price Data
Fiscal Period Data
Desired Output
Solved! Go to Solution.
Hi @Matt91
Have you tried the generate rows tool? Basically generate a row for each day in the start/end date range, and join up to the period data. Your data appears to have gaps/doesn't make sense. For instance there are pricing records with a start date that is after the end date.
Hopefully this gets you on the right track.