Calculate a cumulative total using 2 tables and different date granularity

[GOLEARY735]

Hi all. 

Been searching for an answer to this for hours! Hope someone can help. 

I have some data coming in from a database which tells me components that I have on order and their delivery dates (days). There can be multiple rows for the same components due in on different dates. Table is called POPOrderReturnLine

I have another table which shows me what I have in stock. Table is called StkBalance

I have other data coming in from an excel file which tells me which components are used on which BoMs (some are common across BoMs) and when they're planned to be consumed. This means I have multiple rows of the same component part consumed at different dates in different qntys throughout the year.

I've created a relationship between the date columns in both tables to a 'Calendar' date table that spits info back out at me in weeks, and I've created a 'Planned Consumption' sheet and a 'Planned Goods Receipt' sheet:

This is all good info but what I need to create is a table that will show me the week number that I'll run out of stock of each component. So I need to be able to calculate a cumulative total of each component for each week of 2025, if that value is a negative value, I know I either need to order more in before that date, or amend my production schedule. 

Appreciate you reading! Please let me know if I've missed any information off that you need to be able to help.  

Thanks. 

G

[Anonymous]

Hi @GOLEARY735 

To better assist you, please provide sample data that fully covers your issue (in the form of table or pbix file) and the expected outcome based on the sample data you provided. Please remove any sensitive data in advance.

Best Regards,

Jayleny