This blog was originally posted to the Piraeus Consulting blog on September 2nd, 2015 and may contain out-of-date information.
Depending on the insights desired, presenting data models as cumulative, rolling totals – as opposed to showing a Grand Total broken out over periods of time – can be an invaluable tool in analyzing key business decisions, spotting the difference between real trends and clear outliers, and crafting longer-term strategies.
In order to utilize this specific functionality within a PowerPivot or BISM tabular model, Initial setup is required. First, be sure to have a well-constructed Date table as well as an adjoining fact table to which the Date table’s unique key can be joined. You’ll also need to ensure that the Date table is marked as such, as illustrated below.
Last, but not least, prepare the Data table’s columns to sort as preferred; specifically a numbered column that puts weeks, months, quarters, or other periods of time into a numeric order. This will ensure that your data displays properly in Excel, PowerView, or whatever its final destination may be.
Once setup is complete, it’s time to create your measures. There are several different approaches depending on specific needs. Check out Javier Guillen’s post on the subject for further examples. No matter the scenario, the general approach will be the same; There will be little surprise, even those with minute knowledge of DAX, that the heavy lifting will be done with the CALCULATE function: the ‘Swiss Army Knife’ of designing tabular data models. The following example will use a very simple dataset with just three columns – Daily Sales, New Members and Date.
The basic formula used for the measures in this example is quite simple: a straightforward SUM of both the Daily Sales and New Members columns, along with filtering within the CALCULATE function, turning said SUM into a running total. Here’s what the measure used in our example looks like:
Total Sales :=
SUM ( [Daily Sales] ),
ALLSELECTED ( ‘Date'[Date] ),
‘Date'[Date] <= MAX ( ‘Date'[Date] )
Depending on specific needs, a SUMX , or other mathematical functions, might be preferable to a straight SUM. Other filters can be added to the date filtering within the CALCULATE bringing increased granularity to the cumulative measure as well. Like most functions involving CALCULATE, this method works equally well with datasets of varying complexity.
Now that the measures are created, let’s put them to work! Here’s how our measures show up in a simple pivot table, broken out by Year and Quarter:
In contrast, here’s how things look in PowerView with Total Membership alongside New Members by Year shown in the chart above, and Daily Sales proportionate to Total Sales broken out by Year and Quarter below:
Cumulative measures such as these can offer tremendous insight when used correctly, and can be fine-tuned to provide the correct analysis thanks to the flexibility, and filtering power, of CALCULATE.
Nearly any model designed to gather data over long periods of time can benefit from their inclusion.
By: Aaron Campaeu | Consultant, Solutions and Development
Latest posts by Aaron Campeau (see all)
- 5 Tips for Optimizing SSAS Tabular for Power BI - January 23, 2017
- The How and Why of Cumulative Totals in Tabular Data Models - January 10, 2017