Calculating inventory over time, based on future requirements

[Anonymous]

Hey folks, been struggling with this one for a while, but can't conceptually figure out the best way to do it.

I have three tables:

• Current list of part #s and current on-hand inventory
• 
• List of the next 52 weeks, with columns containing a count of each specific type of site opening that week
• 
• List of part #s, and what site requires what type of part, and the quantity
• 

My end goal here is a table going week by week, broken out by each part #, that has the current inventory level for that week.

I don't expect someone to give me the answer, as I'll keep trying to figure this out. But I'm thinking conceptually I'm going about this wrong. I can roughly figure it out using SQL (which I'm more comfortable with), and I'm not sure if I should be trying that same concept here, or going about it a different way.

Any and all insight is greatly appreciated.

[Anonymous]

So I did end up figuring this out, but I have no clue if it's "effecient" or not (I assume not)

Essentially I crossjoined the inventory and schedule tables, then added some calculated columns to determine total consumption per week,  and calculate a running sum.

[Anonymous]

Hi @Anonymous,

I'm glad to hear you found the workaround. Did you mind sharing the resolution here? I think they may help other community users who faced a similar scenario.

BTW, you can also take a look at the following link start date, end date parts to know more about analysis based on multiple date fields:

Before You Post, Read This 
Regards,

Xiaoxin Sheng