Home > Pivot > Calculation > Fiscal Year Show Fiscal Year in Pivot Table 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. Also see how to compare weekdays in a fiscal year pivot table: Fiscal Year Weekdays - Pivot Table

Introduction

There are two pivot table examples in this tutorial:

1. sales totals by fiscal year and month
2. fiscal year to date and fiscal month to date amounts

Workbook Setup

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

SalesData Sheet

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.

Lists_FY Sheet

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.

Totals for Fiscal Year and Month

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.

Calculate the Fiscal Year

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:

=YEAR([@OrderDate])+(--MONTH([@OrderDate])>=FYStart)

• The year is calculated, based on the order date.
• If the order month is greater than or equal to the fiscal year start month, 1 is added to the order year. (The two minus signs are a double negative, and they convert the TRUE result to a 1)

Calculate the Fiscal Month

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.

Create a Months Lookup Table

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.

Calculate Fiscal Month in Lookup Table

In the sample file, the third column is a formula, so it will automatically adjust if the Fiscal Year start month is changed.

=IF(E2<FYStart,12,0)+(E2-FYStart+1)

• The formula compares the month number, to the FYStart month. If the month number is lower, 12 is used, otherwise zero is used.
• Then, the FYStart month is subtracted from the month number, and 1 is added.

Using January as an example:

1. The month number is 1, which is less than the FYStart month of 4, so the first part of the formula returns a 12.

   IF(1<4,12,0) = 12

2. In the second part of the formula, the month number, 1, has the FYStart (4) subtracted, and one is added. The result is minus 2.

   (1 - 4 + 1) = -2

3. Combine both parts of the formula, and the result is 12 - 2 = 10, so January is fiscal month 10.

   12 + (-2) = 10

Calculate Fiscal Month in Source Data

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:

=INDEX(FM_List,MONTH([@OrderDate]))

• The INDEX function returns the fiscal month from the FM_List range, based on the month number of the order date

Show Fiscal Year and Month in Pivot Table

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,

• Fiscal Year is in the column area
• Fiscal month is in the Row area
• Total Price field is in the Values area.
• Category field is in the Report Filter area.

Fiscal Year to Date

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.

Select a Year and Month

On the PivotFYTD sheet, there are drop down lists where you can select a year and month.

• The Year cell is named Yr_Sel
• The Month cell is named Mth_Sel

  

To the right of those cells, the Fiscal Year and Month are calculated.

• The Fiscal Year cell is named FY_Sel
• The Fiscal Month cell is named FM_Sel

  

Calculate the Fiscal Year to Date Amount

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)

• The SUMIFS function will return the amount in the TotalPrice column, if the fiscal year matches the selected fiscal year, and the fiscal month is less than or equal to the selected fiscal month.

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.

Calculate the Fiscal Month to Date Amount

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:

=SUMIFS([@TotalPrice],[@FY],FY_Sel,[@FM], FM_Sel)

• The SUMIFS function will return the amount in the TotalPrice column, if the fiscal year matches the selected fiscal year, and the fiscal month matches the selected fiscal month

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.

Show FYTD and FMTD in Pivot Table

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

Update the Pivot Table

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:

• right-click on the pivot table, and click the Refresh command.
• Or, use a macro, to automatically refresh the pivot table, if the year or month are changed

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

Download the Sample File

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.

Related Tutorials

Fiscal Year Calculations

Fiscal Year Weekdays - Pivot Table

Budget Forecast

CHOOSE Function

Group by Fiscal Year