Introduction

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:

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

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

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.