A common Measure that you’ll probably find useful in PowerPivot or SSAS Tabular Models is finding running totals. For example, you may want to see total sales of a product as it accumulates over time, or for inventory models the total on hand at a given time. You can find more tips and tricks at my blog, www.bipatterns.com.
Let’s start with a base measure in a very simple pivot table. Total Sales := CALCULATE (SUM ( FactSales[SalesAmount] ))
Now lets take our first attempt at computing a running total. This is the most intuitive formula, but it has one common pitfall that isn’t necessarily easy to see right away. Cumulative Total Sales := CALCULATE ( [Total Sales], FILTER ( ALL ( DimDate[Datekey] ), DimDate[Datekey] <= MAX ( ( DimDate[Datekey] )) ) ) Key parts of the Formula: The use of ALL(DimDate[DateKey]) results in the current context being ignored, so dates outside of the current pivot row context will be analyzed. The second key step is the comparison of DimDate[Datekey] <= MAX ( ( DimDate[Datekey] ). This means that all dates in the DateKey column that are before the current pivot table row context will be calculated.
If we put this measure on a table, we’ll get the correct numbers but we will have one issue remaining.
The formula returns a number for dates that have no sales. We need to add some error handling, which is outlined below. Cumulative Sales (Correct) := IF ( COUNTROWS ( FactSales ) > 0, CALCULATE ( [Total Sales], FILTER ( ALL ( DimDate[Datekey] ), DimDate[Datekey] <= MAX ( ( DimDate[Datekey] )) ) ), BLANK () )
The IF Function checks to make sure that there are sales in the current selected context, otherwise returning blank. You can see the difference between the two measures below: