In a pivot table, there's no built-in option for grouping by fiscal year. See how to calculate fiscal year and month in the source data, and show the results in a pivot table
There are two pivot table examples in this tutorial:
In the sample file, there are two pivot table sheets, and both use the source data from the SalesData sheet. There is also a Products table, and lookup tables for the fiscal year calculations
On the SalesData sheet, there is a named table -- Sales_Data -- that contains sales records. Each record has an order date, and that field will be used to calculate the fiscal year and fiscal month.
This table is the source data for both pivot tables.
On the Lists_FY sheet, there are formulas and named ranges used in calculating the fiscal year. There is also a cell where you can enter the month number in which the fiscal year starts.
In this example, the fiscal year starts in April, so 4 is typed in cell B2 -- that cell is named FYStart.
In the first pivot table, the total sales are shown for each fiscal year and month. New columns are added in the source data, then those fields are used in the pivot table.
After the fiscal year start month has been entered, you can calculate the fiscal year.
In the Sales Data, a column has been added, to calculate the Fiscal Year for each record. Here is the formula:
To calculate the fiscal month, there is a lookup table. The fiscal months can be manually entered, or use a formula, for more flexibility. Then, a fiscal month calculation will be added to the source data.
On the Lists_FY sheet, there is a list of months, with 3 columns -- Month, Month Number, and Fiscal Month Number. The fiscal starting month is highlighted in the screen shot below.
The month table will be used as a lookup, for a Fiscal Month column in the SalesData sheet.
The grey cells are a named range -- FM_List
The first two columns are manually entered, and the fiscal months could also be manually entered.
In the sample file, the third column is a formula, so it will automatically adjust if the Fiscal Year start month is changed.
Using January as an example:
IF(1<4,12,0) = 12
(1 - 4 + 1) = -2
12 + (-2) = 10
After the months lookup table has been built, you can calculate the fiscal month in the source data.
In the Sales Data, a column has been added, to calculate the Fiscal Month for each record. Here is the formula:
After you calculate the fiscal year and fiscal month, you can use those fields in a pivot table, to summarize the data.
In the screen shot below, the pivot table from the PivotFY sheet is shown,
In the sample file, there is another pivot table sheet -- PivotFYTD. Instead of simply showing the fiscal year and month, it shows the fiscal year to date and fiscal month to date amounts, for a selected year and month.
To prepare for this type of pivot table summary, more calculations are added in the source data, and month and year selectors are added to the PivotFYTD worksheet.
On the PivotFYTD sheet, there are drop down lists where you can select a year and month.
To the right of those cells, the Fiscal Year and Month are calculated.
In the Sales Data, a column has been added, to calculate the Fiscal Year to Date amount for each record. Here is the formula:
=SUMIFS([@TotalPrice],[@FY],FY_Sel,[@FM],"<=" & FM_Sel)
So, if the selected year is 2014, and the selected month is May, only the amounts from April and May 2014 would be returned, because the fiscal year starts in April.
In the Sales Data, a column has been added, to calculate the Fiscal Month to Date amount for each record. The formula is similar to the Fiscal Year to Date formula:
So, if the selected year is 2014, and the selected month is May, only the amounts from May 2014 would be returned, because that is the selected month.
In the pivot table, the FYTD and FMTD fields have been added to the Values area. They show a summary of the amounts for the year and month selected at the top of the worksheet -- in cells Yr_Sel and Mth_Sel
If you change the selections in the drop down lists, the formulas in the source data will automatically change, to show the correct FYTD and FMTD amounts.
However, the pivot table does not refresh automatically, so you would need to either:
In the sample file, there is an event procedure that runs when you change the Yr_Sel cell, or the Mth_Sel cell. To see the code, right-click the sheet tab, and click View Code.
Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo errHandler Select Case Target.Address Case Range("Mth_Sel").Address, _ Range("Yr_Sel").Address Me.PivotTables(1).RefreshTable Case Else 'do nothing End Select exitHandler: Exit Sub errHandler: MsgBox "Could not update pivot table" Resume exitHandler End Sub
Click here to download the Fiscal Year Pivot Table sample file. It is zipped, and in xlsm format, and contains macros. Enable macros when you open the file, if you want to test the code.
Last updated: September 12, 2016 2:48 PM
Contextures RSS Feed