Dynamic Calculation of Inventory with #dax in #powerpivot and #ssas #tabular

I recently wrote an article on SQLBI describing the dynamic calculation of stock inventory in DAX. With this technique, you can avoid creating a snapshot fact table just to store the inventory stock for every day and every product, creating a table that in certain business is huge and expensive, especially if you have to load it in memory.

I also performed some performance comparisons between the two approaches. I have to say that I would not suggest one option against the other, unless I can do some test on a real set of data. The problem is that the real convenience depends on data distribution and density. Moreover, performance differences also depends on the query, and I have found that the traditional snapshot-based approach is faster when performance are always good (queries below one second), but becomes slower when there are queries returning large result set. I still don’t have a clear explanation of the reason why there are these differences, so if you have any feedback based on experience on different data sets, you are more than welcome if you will share it here!

You can read the article and download the sample workbooks (for both Excel 2010 and Excel 2013) following this link.