PowerPivot 6 – The Joy of Sets

By Stuart Box

Excel Power BI Resources PowerPivot

Time Intelligence Functions

One of the most powerful features of PowerPivot is the ability to analysis your data across time periods.  To use the Time Intelligence functions, you must have a table in the PowerPivot window that comprises all the dates from the date range of your data.  For example, my wine sales range is between 1st September 2010 and the current date.  Therefore, I’ve created a Date Table that lists all the dates between these two dates.  I’ve also created another column that returns just the year, month number and month name. To achieve these, use these Excel expressions:-Year – Year(A2) Month – Month(A2) MonthName – Text(A2,”mmm”) These columns are useful to analyse across years and months but the only mandatory column is the first one.   My Date Table looks like this. The dates for my wines sales in the SALEDATE field are then related to the DATEKEY field in the Date Table. All of the Time Intelligence functions use the DATEKEY field from the DateTable.  This is because the DateTable will show all dates filtered by the specific function (e.g. all year to date dates) whereas the analysis table (e.g. Winesales table) will only show dates filtered according to the Filter Context in the Pivot Table. Because these functions modify the filter context, they must be nested in the CALCULATE function. Here are some examples of Time Intelligence expressions:-


=CALCULATE([totalsales], PREVIOUSMONTH(DateTable[DateKey]))






=CALCULATE([TotalCases],DATESBETWEEN(DateTable[DateKey],BLANK(),LASTDATE(DateTable[DateKey]))) Any shifted dates:-=CALCULATE([totalsales],DATEADD(DateTable[Datekey],-1,month/year/quarter/day))

Creating Key Performance Indicators

Key Performance Indicators (KPI) are designed to help you compare the current value of a measure (base value) against a defined target (target value).  They do this by using an indicator, for example traffic lights, that can show if the target has been reached. For example, say you wanted to compare the Previous Month’s sales to the Current Month’s sales (the Total Sales filtered for the current month). To create a KPI you must first create at two calculated measures. The first one being the base value, the Previous Month’s values , and the second being the target which will be the Current Month values (TotalSales). To create the KPI, right click in the Field list on the measure that will be the base value e.g. PreviousMonth and select Create KPI. In the KPI window, in Define Target select the comparison measure e.g. TotalSales.  In the threshold sliders, move these to the correct % that will define each indicator e.g. If the Previous Month’s values are 100% or more of the values for this month, the indicator will be a red traffic light. Notice the colour indicators have been reversed in the bottom right of this window.

Using PowerPivot Sets

When of the great benefits of using PowerPivot is that you can create “Sets” of data.  Creating “Sets” means that you can pick off certain combinations of rows and/or columns of data that would be impossible (or time-consuming) using the normal filters.  These “Sets” of data can then be reused in the PivotTable whenever required. To create a set, click into your PowerPivot PivotTable and on the Excel Ribbon, under PivotTable Tools and on the Options tab, click on the “Fields, Items & Sets” icon and then select, for example, “Create Set Based on Column Items….” In the New Set window, give your Set a name and then simply remove the columns or rows you don’t want using the Delete Row button. Notice the Set then appears in the PowerPivot Field List. So there it is, a very brief tutorial around PowerPivot. I hope you found it useful. Of course, we've only just scratched the surface of what PowerPivot can do, but I hope I've shown a little of what this brilliant Excel add on can do. Burningsuit provides Training and Consultancy on PowerPivot and all aspects of Excel and Microsoft applications. We specialise in on-site or online instructor lead Training. Contact us to find out how we can help you and your business or organisation be more productive and effective.

Leave a Reply