rolling inventory level over time

[dannecr]

Hi community,

I got kind of an easy question for you but somehow I am stuck at the moment.

Maybe you can help me out with my issue? 🙂

I do have the following table as the data source:

Article	goods out - date	goods out - quantity
ABC-1	Dez 22	5000
ABC-1	Feb 23	790
ABC-1	Mrz 23	3951
DEF-2	Jan 23	33
DEF-2	Feb 23	700
DEF-2	Apr 23	5000

Showing for 2 articles the goods out dates & qty.

So from article ABC-1 I would have today a stocklevel of a total of 9741 pcs.

By end of December 5000 pcs are shipped out. So the stocklevel would have reduced to only 4741 pcs.

This continues till end of February where the last items of this article are shipped out and the stocklevel drops to 0.

I am now looking for a way to show this in a new table.

The new table should preferably look somehow like the example below:

Article	ABC-1	DEF-1
Okt 22	9741	5733
Nov 22	9741	5733
Dez 22	4741	5733
Jan 23	4741	5700
Feb 23	3951	5000
Mrz 23	0	5000
Apr 24	0	0

Any suggestions on how to set up such kind of a table?

The amount of possible is not limited; also the goods out days and the qty are not limited.

(Just limited in this example here for a better understanding)

Would be highly appreciated if you could support me 🙂

Thanks in advance for your outstanding support.

[Anonymous]

Hi @dannecr ,

Is there a calendar table in the model? Or a column for sorting [goods out - date]? What is [goods out - date]s data type?

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[dannecr]

Hi Gao,

thanks for your message.

So far there is not a calendar table included in the model; also a column for sorting [goods out - date] is not yet implemented.

The [goods out - date] column is originally a date type but was converted to a text type. E.g. "15/12/2022" is converted to "Dez 22" --> Date.ToText([goods out - date], "MMM.yyyy"))

The input table has only the 3 columns as shown in the example.

[Article] [goods out - date] [goods out - quantity]

All [goods out - date] values are in the future.

I wanna show the current stock level based on [goods out - quantity].

And once- goods are shipped out I want to display the new stock level.

Thanks 🙂