Calculating Cumulative Values in Microsoft BI Tools
In working on a business intelligence project a common situation you will find yourself in is having to come up with a cumulative value (running value) calculation. The challenge that you will find is that depending on what product you are working with the calculation that you need is going to be different. In this post I will compare how this can be done with T-SQL, Excel, SSIS, SSRS, MDX, and DAX. Along the way I will provide some additional reference links to other options, content, and I will point out some differences in how the totals are being calculated so you will know what to expect with the results.
In these examples I will be working with the Contoso Retail Data Warehouse data sample. These examples below are not necessarily going to be the optimal approaches, just showing how you can go about getting at the desired result.