This month we’ll look at a question sent in by a reader: How can you create a pivot table that will show a rolling 12 months of sales? Ideally, you’d be able to refresh the pivot table and have it update without having to remember to specify the filter again for each day or month.
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.
4 comments
Issac Thomas April 6, 2019 AT 11:23 am
Nice article!
sean neil April 5, 2019 AT 5:11 pm
I want to learn Excel
sean neil April 5, 2019 AT 5:09 pm
I’m interested in pivot table and v look up
AyeMyatMon April 5, 2019 AT 2:34 pm
Thank you so much
Comments are closed.
You may also like
FAQs
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.
How do you do a financial year in a pivot table? ›
Use the Fiscal Year Column in your Pivot Table
- Select any cell in your data source, and then from the Ribbon's Insert tab, select Pivot Table. ...
- If necessary, add any needed fields to the Pivot Tables Row, Column, and Value sections.
- Add the Fiscal Year field to either the row or column section of the Pivot Table.
How to create a running total in Excel
- Start with =SUM. Click on the cell where you want your running total to begin. Next, select the SUM function on that cell. ...
- Create a running total formula. You must use the dollar sign in this formula, even if the numbers you're tallying are not dollar amounts.
Right-click any of the dates in the pivot table. Select Group... from the context menu. In the Grouping dialog, select Years, Months and Days. Click OK.
How do you calculate a 12-month rolling period? ›
The 12-month rolling sum is the total amount from the past 12 months. As the 12-month period “rolls” forward each month, the amount from the latest month is added and the one-year-old amount is subtracted. The result is a 12-month sum that has rolled forward to the new month.
What is a rolling 12-month period example? ›
Under the ''rolling'' 12-month period, each time an employee takes FMLA leave, the remaining leave entitlement would be the balance of the 12 weeks which has not been used during the immediately preceding 12 months. • Example 1: Michael requests three weeks of FMLA leave to begin on July 31st.
Do financial analysts use pivot tables? ›
A pivot table allows you to organize, sort, manage and analyze large data sets in a dynamic way. Pivot tables are one of Excel's most powerful data analysis tools, used extensively by financial analysts around the world.
How do you convert calendar year to financial year? ›
If the date's month is before the start of the fiscal year: Fiscal year is the same as the Calendar year.
...
Using the date August 1, 2022 as an example, for fiscal year starting in July, here is the arithmetic for the calculation:
- Year: 2022.
- Month: 8.
- Start Month: 7.
- 8 >= 7: TRUE.
- 2022 + 1 = 2023.
Using this method, the employer will look back over the last 12 months from the date of the request, add all FMLA time the employee has used during the previous 12 months and subtract that total from the employee's 12-week leave allotment.
Why can't i group months in pivot table? ›
If any cells within your data field include errors or text, the date grouping feature will not work, and the grouping feature will tell you that you cannot group that selection. This is often due to issues in the date field your pivot table is using.
In a PivotTable, click the small arrow next to Row Labels and Column Labels cells. Click a field in the row or column you want to sort. on Row Labels or Column Labels, and then click the sort option you want. To sort data in ascending or descending order, click Sort A to Z or Sort Z to A.
Is rolling 12 months the same as trailing 12 months? ›
LTM (Last Twelve Months), also sometimes known as the trailing or rolling twelve months, is a time frame frequently used in connection with financial ratios, such as revenues or return on equity (ROE), to evaluate a company's performance during the immediately preceding 12-month time period.
What is the difference between YTD and rolling 12 months? ›
12 mtd goes back 12 months, whereas a ytd is from the first day of the current year (calendar, fiscal, whatever) to the current day.
What is a 12 month rolling return? ›
The rolling returns formula is quite simple; add the returns from each calendar year during the desired period (3 years, 5 years, etc) and divide the sum by the number of years. For example, if the ABC Fund had returned 6% from March 1, 2021 to February 28, 2022, the fund had a one-year rolling return of 6%.
What does 12 consecutive months mean? ›
Consecutive Months: Months occurring consecutively (not the same as a calendar year). Example: If your policy covers two cleanings in a 12-consecutive-month period, the first cleaning occurs July 5, 2024 and the second January 5, 2025 - patient is not eligible for this service again until July 5, 2025.
How do you explain a rolling year? ›
A rolling year is the year immediately preceding the start of a period of sickness.
What is a trailing 12 month period? ›
Trailing 12 months (TTM) is a term used to describe the past 12 consecutive months of a company's performance data, that's used for reporting financial figures. The 12 months studied do not necessarily coincide with a fiscal-year ending period.
What are pivot tables not good for? ›
Pivot tables generally work great, but they can run into snags if certain cells they are attempting to read are blank or filled in the wrong format. Your original spreadsheet will have to be well-crafted, with clear column headings and consistent formatting for a pivot table to work well.
What are downsides of using pivot tables? ›
Disadvantages of Using Pivot Tables
Mastering pivot tables takes time – Sure, creating a pivot table requires a few clicks inside Excel but truly mastering the tool takes time. First-time users of pivot tables might see it as confusing and overwhelming.
What is the most accurate pivot indicator? ›
Short time frames like 1-minute, 2-minute and 5-minute are the best for pivot point indicator. This makes pivot points more preferable to day traders. Pivot point indicators are amongst the best tools when accuracy is concerned. This is because of the fact that pivot points are so widely used.
Use finance templates for Excel to compare mortgage types, assess the pros and cons of switching investments, or to calculate whether a new car is in your future. Small business owners can use financial templates for a number of financial tasks, from creating yearly income statements to forecasting their cash flow.
How do I derive a financial year from a date in Excel? ›
YEAR(A2)-1 & "/" & RIGHT(YEAR(A2),2), So in the above formula, the date would return (2020/21) if the date was 3rd April 2021. This is because the 3rd of April is before the new tax year of the 6th April. Hence, it's included in the previous financial year calculation.
What is the difference between a financial year and a calendar year? ›
Key differences between fiscal year vs calendar year
A calendar year always begins on New Year's Day and ends on the last day of the month (Jan. 1 to Dec. 31 for those using the Gregorian calendar). A fiscal year can start on any day and end precisely 365 days later.
Is YTD financial year or calendar year? ›
Year to date (YTD) is a term covering the period between the beginning of the year and the present. It can apply to either calendar or fiscal years. Your fiscal year might not necessarily begin on 1st January but no matter the dates, YTD covers the first day of the year in question up until the day of calculation.
Why financial year is required for instead of calendar year? ›
Using a different fiscal year than the calendar year lets seasonal businesses choose the start and end dates that better align with their revenue and expenses. This means a fiscal year can help present a more accurate picture of a company's financial performance.
How do you calculate trailing 12 month return? ›
TTM price/earnings ratio.
Also commonly referred to as trailing P/E, this measures a company's P/E ratio over the previous 12 months. It's calculated by dividing the current stock price by the earnings per share (EPS) for the last four quarters.
How do you calculate 12 months? ›
The easiest way to calculate data from the trailing 12 months is to add by the previous four quarters, the three-month periods into which the fiscal year is broken up. Start with the most recent quarter–for instance, to make a TTM calculation in July 2020, one would begin with Q2, which ended in June 2020.
Does trailing 12 months include current month? ›
In other words, if you are running your trailing 12 months reports in July 2020, your starting date will be July 1, 2019. Your ending date will be the last day of the month just completed — in this example, June 30, 2020.
How do I autofill 12 months in Excel? ›
Use the Fill Handle
- Select the cell that contains the first date. Drag the fill handle across the adjacent cells that you want to fill with sequential dates.
- Select the fill handle. at the lower-right corner of the cell, hold down, and drag to fill the rest of the series.
Add or subtract months from a date with the EDATE function
- For this example, you can enter your starting dates in column A.
- Enter the number of months to add or subtract in column B. ...
- Enter =EDATE(A2,B2) in cell C2, and copy down as needed.
1. It can be a time-consuming venture. For most programs, pivot tables can create the data needed to understand metrics, but the tool doesn't include calculation options in many instances. That means the collected data must be manually calculated or equations must be manually inputted and that takes time.
How do I create a date hierarchy in a pivot table? ›
Follow these steps:
- Open the Power Pivot window.
- Click Home > View > Diagram View.
- In Diagram View, select one or more columns in the same table that you want to place in a hierarchy. ...
- Right-click one of the columns you've chosen.
- Click Create Hierarchy to create a parent hierarchy level at the bottom of the table.
Formula Method
- Open your spreadsheet in Microsoft Excel and locate the date you wish to increment by one month.
- Type "=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))" without quotes in a blank cell, replacing "A1" with the reference to the cell containing the date.
To get the fiscal year for the given date in cell C6, enter the following formula in cell C8: =YEAR(C6) + IF(MONTH(C6)>=C4,1,0)
How do you get Excel to AutoFill months? ›
Put the mouse pointer over the bottom right-hand corner of the cell until it's a black plus sign. Click and hold the left mouse button, and drag the plus sign over the cells you want to fill. And the series is filled in for you automatically using the AutoFill feature.
How can you quickly enter all 12 months in columns in Excel? ›
Hover the mouse over the lower right corner of the cell until you see the Fill Handle. With the LEFT mouse button pressed, drag to select the cells to autofill with month names and release the mouse button.
What are trailing 12 month financials? ›
Trailing 12-month, or TTM, refers to the past 12 consecutive months of a company's performance data used for reporting financial figures. By consistently evaluating trailing 12-month numbers, company financials can be evaluated both internally and externally without regard for the artificiality of fiscal year-end.
How is 12 months calculated? ›
Step 1: Find the Last Annual Filing Financial Data. Step 2: Add the Most Recent Year-to-Date (YTD) Data. Step 3: Subtract the Prior Year YTD Data Corresponding to the Prior Step.
How do you calculate 12 month average? ›
12-month rolling average means the sum of the average rate or concentration of the pollutant in question for the most recent complete calendar month and each of the previous 11 calendar months, divided by 12. A new 12-month rolling average shall be calculated for each new complete month.
Do you count 12 months as a year? ›
A year is divided into 12 months in the modern-day. The months are either 28, 29, 30, or 31 days long.
