Home > Pivot > Calculation > Fiscal Year Fiscal Year Weekdays in Pivot TableSee 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. |
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:
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.
In the sample file, there are 3 sheets used for the report -- SalesData, SalesRpt and Admin
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.
The Admin sheet has 4 named cells, and those names will be used in other formulas.
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)
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))
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)
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.
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
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:
In each Fiscal Week, the amounts for each weekday can be easily compared.
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.
=[@Date]-[@FYStart]+1
The column shows the day of the Fiscal Year, for each sales date.
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.
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.
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.
Then, before you filter the pivot table, you can type a different date in cell H2, to see its Fiscal Year Day.
Click here to download the Fiscal Year Weekday Pivot sample file. It is zipped, and in xlsx format, and does not contain any macros.
Last updated: February 24, 2023 4:17 PM