Home > Pivot > Calculation > Fiscal Year Fiscal Year Weekdays in Pivot Table See how to compare sales year over year, by weekday, instead of calendar date. Add formulas and a pivot table, to compare weekdays in any fiscal week. Thank you to Tim Easter for sharing this method.

Introduction

In some businesses, such as retail, the day of the week can have a significant impact on sales. In those businesses, it's not helpful to compare sales for a specific date. For example -- April 3rd:

• was a Sunday in 2016, when sales are brisk
• fell on Monday in 2017, when stores are quiet

To avoid that problem, this sample workbook calculates a fiscal week, and compares sales by weekday, within each fiscal week. The setup instructions are below, and download the sample workbook to see the completed file.

Workbook Setup

In the sample file, there are 3 sheets used for the report -- SalesData, SalesRpt and Admin

SalesData Sheet

On the SalesData sheet, there is a named table -- Sales_Data -- that contains sales records for two different years. Each record has an order date, and that date will be used to calculate the fiscal year, fiscal period and fiscal week.

This table starts with 2 fields -- the sales date and the sales amount. More columns will be added, with Fiscal Year calculations.

Admin Sheet

The Admin sheet has 4 named cells, and those names will be used in other formulas.

• FYNum: month number in which Fiscal year starts - April, in this example
• AddMths: =IF(FYNum=1,0,12-FYNum+1)
• MinusYrs: =IF(FYNum=1,0,1)
• WDRT: Select a Return Type for the WEEKDAY function

Calculate the Weekday

First, to find the weekday name for each sales date, add a new column in the Sales Data table, with the heading, Weekday

In the first data row, enter this TEXT formula in the Weekday column, to show the short name for the weekday ("ddd"). Or, use "dddd" for the long weekday name.

=TEXT([@Date],"ddd")

NOTE: Formulas are in a named table, and use table references ([@Date]) instead of cell references (A2)

Calculate the Fiscal Year

Next, add another column, with the heading "FYr", to calculate the Fiscal Year for each record.

In the first data row, enter this formula:

=YEAR(EOMONTH([@Date],AddMths))

• The EOMONTH function finds the last day in the month that is "AddMths" number of months after the sales date
• Then, the year for that new date is calculated, to get the Fiscal Year.

Calculate Fiscal Year Start Date

Next, add another column, with the heading FYStart, to calculate the Fiscal Year start date. In the first data row, add this formula:

=DATE([@FYr]-1,FYNum,1)

Calculate Fiscal Start Week Sunday

The next step is to calculate the first day (Sunday) in the week of the Fiscal Year start date.

The formula will use the WEEKDAY function, which returns a weekday number for the selected date. In this example, the default Return type (1) is used, and you can choose a different option on the Admin sheet, in the WD_RT cell.

After you select a Return Type, go back to the SalesData sheet, and add a new column with the heading FYWkStart.

In the first data row, enter this formula:

=[@FYStart]-WEEKDAY([@FYStart],WD_RT)+1

In the screen shot below, April 1, 2016 is a Friday (weekday #6), and March 27th is the Sunday of that week.

The formula subtracts 6 from the April 1, 2016 date, and then adds 1, to find the week's start date.

Calculate Fiscal Week and Period

Finally, two more columns are added to the SalesData table. These formulas calculate the Fiscal Week, and the Fiscal Period. There are 13 periods in the fiscal year, with 4 weeks in each period.

FYWk: =INT(([@Date]-[@FYWkStart])/7)+1

FYPer: =INT(([@FYWk]-1)/4)+1

Show Year Over Year in Pivot Table

Next, create a pivot table from the Sales Data, and add the fiscal fields, to summarize the sales data. For each Fiscal Week, show the total sales for each Weekday.

In the screen shot below, the pivot table from the SalesRpt sheet is shown:

• Fiscal Period (FYPer) is in the Report Filter area.
• Fiscal Year (FYr) is in the column area
• Fiscal Week (FYWk) is in the Row area
• Weekday is in the Row area
• Sales amount is in the Values area

In each Fiscal Week, the amounts for each weekday can be easily compared.

Filter for Year To Date

If you want to filter the pivot table up to a specific day in the fiscal year, follow these steps to add a YTD filter. Another field is added to the Sales Data, and then used in the pivot table.

Add the New Field for Fiscal Day

1. Add a new column to the SalesData table, with the heading FYDay.
2. In the first data row, enter this formula:

=[@Date]-[@FYStart]+1

The column shows the day of the Fiscal Year, for each sales date.

Update the Pivot Table

Next, follow these steps to add the field to the pivot table. The field will be added to the Row area, because there are better filter options there.

1. Right-click on the pivot table, and click Refresh
2. In the Pivot Table Field List, add the FYDay field to the Row area, after the Weekday field
3. Right-click on any number in the FYDay field, and point to Expand/Collapse in the pop-up menu
4. Click on the Collapse Entire Field command, to hide the fiscal year day numbers.

To filter by Fiscal Year day, click the arrow in the FYDay heading. Either use the check boxes, or the Label Filters, such as Less Than, or Less Than or Equal To.

Note: You could use Slicers for Fiscal Year Period and Fiscal Year Day, if you prefer.

The pivot table shows the results for the selected Fiscal Year days.

Fiscal Day Calculator

To make it easy to determine the Fiscal Year day for any date, put a Fiscal Day Calculator at the top of the worksheet, above the pivot table.

1. In cell H2, enter a date
2. In cell I2, enter this formula, to calculate the Fiscal Year Day:
3. =H2-DATE(YEAR(H2),FYNum,1)+1

Then, before you filter the pivot table, you can type a different date in cell H2, to see its Fiscal Year Day.

Download the Sample File

Click here to download the Fiscal Year Weekday Pivot sample file. It is zipped, and in xlsx format, and does not contain any macros.

Related Tutorials

Fiscal Year Calculations

Budget Forecast

CHOOSE Function

Group by Fiscal Year

SUM Function

VLOOKUP Function

INDEX and MATCH

Count Functions

INDIRECT Function