How to look up values based on dates in a date range across multiple datasets?

[Matt91]

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

[Luke_C · Accepted 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.

[Matt91]

Hi @Luke_C,

Thanks this absolutely got me on the right track. Data gaps were to do with how I'd generated the sample data.

I added a couple of steps to clean up the data and a multi-row formula to replace null values.  Workflow attached, thanks for your help!