Forum Discussion
Creating a measure that builds upon itself, using a fixed starting point
Greg_Deckler - with some assistance from a brilliant resource on my team, we came up with a solution. Instead of using MAX, we utilized a couple of variables in order to evaluate the sums of the demand and adjustment columns based on the dates between the beginning month (where the last inventory is known) and simply adding those sums to the beginning inventory. So for Feb, it's the sum of the demand/adjustment where month start > jan 2023 and <= feb 2023 plus the beginning inventory. For April, it's the sum of the demand/adjustment where month start > jan 2023 and <= apr 2023 plus the beginning inventory and so on. I can't believe it was so simple...
Greg_Deckler - with some assistance from a brilliant resource on my team, we came up with a solution. Instead of using MAX, we utilized a couple of variables in order to evaluate the sums of the demand and adjustment columns based on the dates between the beginning month (where the last inventory is known) and simply adding those sums to the beginning inventory. So for Feb, it's the sum of the demand/adjustment where month start > jan 2023 and <= feb 2023 plus the beginning inventory. For April, it's the sum of the demand/adjustment where month start > jan 2023 and <= apr 2023 plus the beginning inventory and so on. I can't believe it was so simple...