Calculate future cumulative stock with missing dates

[JFR2022]

Hi,

I want to calculate the cumulative stock per week based on the current stock and the planned consumption in the upcoming weeks and put it in a matrix.

The measure i'm using is:

cumulative stock = calculate([Current stock] - 'Planned'[Planned consumption],FILTER('Date','Date'[Date] >= today()))

Where "planned consumption" is a measure of the sum of the planned quantity in table "Planned".

In some of the weeks there are no orders planned so in my planned consumption table there are dates/weeks missing. 

I'm using a date table which is linked to the planned consumption tabel based on the planned date.

Example of values in table "Planned"

The current outcome of my measure is:

The numbers in green are my desired results.

What do I need to adjust to make this work?

Thanks in advance! 🙂

[JFR2022]

Hi @daXtreme, thanks for your answer.

I It would require me to make another datetable with 365 rows (days) per stock item? I can't add the missing dates in my current planned table because that table is not date driven but order driven.

Does the new table needs to look something like this based on my earlier example? (where "planned quantity" is the sum of column "TargetQuantity" per articlenr in my "Planned" table.

Note: I have more than 500 unique stockitems which are all present in table "stock_per_item" with column "Articlenr". As an example i've added articlenr 35050.

Can you recommend any tutorial or tips to create such a table?

I appreciate your help! 🙂

[daXtreme]

Hi @JFR2022 

If you have missing dates in any of your tables, insert the missing dates into them with figures that will make your calculations correct. To complete your tables, please use Power Query.