Inventory in Power Pivot and DAX: Snapshot vs. Dynamic Calculation

You can implement the calculation of stock inventory over time in several ways in DAX. The xVelocity technology in Power Pivot and Analysis Services Tabular is so fast that you might consider both dynamic calculation and snapshot based calculation, depending on volume and distribution of data.

When you want to analyze stock inventory over time, the traditional approach is creating an Inventory table that contains, for every day, the quantity for every product that is in stock. Such a table makes it very quick retrieving the stock amount of one or more products, because you have to sum only the rows for a single day. The Inventory table is also known as a "snapshot" table, which means that for every day you have a complete description of the stock availability. There should be some batch processing (usually part of the ETL nightly process) that updates this table every day and the size of this table increase every day by the number of products available, assuming you do not store a row for products that are out of stock.