Best Practice for Inventory / Stock Level calculation
Hi,
I am trying to find the best practice for presenting Inventory / Stock levels by day based on underlying transaction data that basically shows product, date, quanitity, where quantity can be positive (stock coming in) or negative (stock going out).
So far I have come up with three different solutions:
1. Some kind of IF-statement combined with a Date Island, e.g. =IF(TransDate<=Max(DateIslandDate))
With 10 000+ products all these solutions (except 1) take quite a long time to run and of course create a lot of rows if run over a couple of years 10 000 * 365 = 3.65 million per year. Alternative 3 gets quite complex if run over more dimensions than just Prod and Date. Alternative 2 is clean, but probably the slowest to run.