When I set out to determine the answer, I first headed to the Date Filter flyout menu in pivot tables, which have around two dozen options for filtering dates. That’s a lot of choices, but there’s nothing in that long list that would provide a rolling 12 months. There are choices for This Year, Last Year, Year to Date, All Dates in Quarter 1, Today, Yesterday, or Tomorrow. None of these handle a rolling 12 months. The closest would be to use the Between filter, but that would require the user remembering to update the settings each time.
If you have Excel 2016 or newer in the Windows versions of Excel, you can solve this problem using some calculated fields written using the DAX formula language. With earlier versions of Excel, you would need to download the Power Pivot add-in in Excel 2010 or pay for the Power Pivot add-in in Excel 2013. But starting with Excel 2016, the functionality that you need is built in to the core Excel product.
CALCULATING VS. FILTERING
This process is different from filtering. Say that your data set had four years’ worth of sales, and you filtered to This Year. The filter would virtually remove all rows that weren’t from this year. If, for example, you had a customer who purchased from you in 2017 and 2018 but not in 2019, that customer wouldn’t appear in the pivot table.
That’s why a calculation is more effective. While keeping all rows intact, the calculation adds a new column that essentially says, “If the dates are in the last 12 months, bring the sales amount to this new column; otherwise put zero in this column.” That means the lost customer from 2017-2018 will appear in your pivot table, but with sales of zero. If that is going to bother you, then use the Customer Filter to remove all customers with sales of zero.
USING THE DATA MODEL
The data set has thousands of rows of sales data on a single worksheet. The relevant columns are Date, Product, and Sales. It’s no longer necessary to format the data set as a table. Select one cell in the data, and use Insert, Pivot Table. In the Create Pivot Table dialog box, check the box to Add This Data To The Data Model.
If your data set wasn’t formatted as a table, it will appear in the PivotTable Fields pane with a table name of “Range.” Otherwise, it will appear with a name such as Table1 or tblSales if you previously renamed the table. Finding the table name is the key to being able to add a calculated field using the DAX formula language. In the PivotTable Fields pane, right-click the table name and choose Add Measure.
In the Measure dialog, specify a Measure Name such as SalesLast365Days. Near the bottom of the dialog, format the field as Currency with 0 decimal places. In the Formula box, enter the formula =Calculate(Sum([Sales]),Filter(Range,Range[Date]<=TODAY() && Range[Date]>TODAY()-365)).
When you click OK to create the measure, a new field will appear in the PivotTable Fields dialog. The field has an italics “fx” icon to indicate that it’s a calculated field. Check the box next to this field to add it to the pivot table.
The formula you entered assumes that your pivot table data is updating daily. If you run the report on April 10, 2019, it will show you sales from April 11, 2018 to April 10, 2019.
Instead, you might want to report full months, ending with the last day of the previous month. In this case, the EOMONTH function will return the date associated with the end of the month. If you’re running the pivot table on April 10, 2019, using =EOMONTH(TODAY(),-1) will return March 31, 2019. There is no equivalent function for First of Month, but you can ask for the End of Month for 13 months ago and add 1 day to get to April 1, 2018.
The formula for sales during the full 12 months ending with the prior month is =Calculate(Sum([Sales]),Filter(Range,Range[Date]<=EOMONTH(TODAY(),-1) && Range[Date]>=EOMONTH(TODAY(),-13)+1)).
If you want to compare the running 12 months sales to the prior 12 months sales, create a new calculation for =Calculate(Sum([Sales]),Filter(Range,Range[Date]<=EOMONTH(TODAY(),-13) && Range[Date]>=EOMONTH(TODAY(),-25)+1)).
CHECKING YOUR WORK
Since these calculations are likely new to you, rearrange your pivot table to put dates in the Rows area and all of the calculated fields in the Values area. Scroll through the various date rows and make sure that the Sum of Sales number from column B is appropriately copied to the other columns in the pivot table. In the figure below, the pivot table on March 11, 2019, is correctly picking up sales from March 12, 2019, as the first sales figure in SalesLast365Days.
Column B shows sales for the last 365 days, including partial months. Column C shows sales from the last complete 12 months. Column D shows sales from 13-24 months ago. As you refresh the pivot table in the future, the calculations for running 12 months will update automatically.
SF SAYS
The ability to write DAX formulas is enabled by adding your data to the Data Model when creating the pivot table.
April 2019
