Hi guys, I am trying to build out a dynamic variable that will calculate the rolling average of the prior seven days of a product deployment, including the current day, which will be used in other graphs. An example of which is: Net Inventory = Day's inventory - [Avg of today and prior 6 days inventory] This variable needs to be dynamic, as in it needs to be capable of changing based on whether considering at the model level, or at the next level up where some models roll up into combination. So for example, lamp and chandelier roll up into lighting, etc. So it would need to be able to calculate the average demand for all three of these contingent on which are displayed in the dashboard, etc.
Does anyone have some suggestions for how to go about doing this?