Pivot Table Compares Weekdays in Fiscal Year

If you’re tracking retail sales year over year, it’s not too helpful to compare sales by calendar date. The dates will fall on different weekdays each year, so slow weekdays will be compared to busy days. To get a better comparison, add a few formulas to the sales data, then use a pivot table to line up the weekdays.

Compare Weekdays in Pivot Table http://www.pivot-table.com/

Compare Calendar Dates

Here’s an example of what happens when you use calendar dates to compare a store’s sales. Weekends are busy in the store, so the Sunday April 3rd are high in 2016. In 2017, April 3rd was on a Monday, and sales are usually slow at the beginning of the week.

pivotfiscalweeks07

Compare Weekdays

To fix this problem, add a Weekday field to the sales data, and a few fiscal year calculations. With those fields in the pivot table, you can compare the weekdays in each fiscal year.

That makes it easier to see that the sales are down a bit in April 2017, which is the start of Fiscal Year 2018 in this example.

pivotfiscalweeks12

Add a Weekday Field

The Weekday field uses a simple TEXT formula to get the 3-letter code for each date’s weekday. If you want the full weekday name, use the format “dddd” instead of “ddd”.

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

pivotfiscalweeks02

Calculate Fiscal Year Start Week

A few Fiscal Year formulas were also added to the sales data, and you can see all of them on the Fiscal Year Weekdays page on my Contextures website.

To line up all the weekdays correctly, the Fiscal Year and FY Start Date are calculated.

pivotfiscalweeks05

The Sunday of the FY Start Date’s week is also calculated. That will ensure that the Fiscal Weeks line up correctly.

pivotfiscalweeks09

Compare in a Pivot Table

Then, after all the Fiscal Year calculations have been added to the sales data table, build a pivot table to compare the year over year sales.

  • Fiscal Period is in the Report Filter area.
  • Fiscal Week and Weekday are in the Row area
  • Fiscal Year is in the Column area
  • Sales Amount is in the Values area

pivotfiscalweeks12

Download the Sample File

To see all the formulas and the pivot table, go to the Fiscal Year Weekdays page on my Contextures website. In the download section, click the link to get the file. The zipped workbook is in xlsx format, and does not contain macros.

Compare Weekdays in Pivot Table http://www.pivot-table.com/

___________________

Save

This entry was posted in Excel Pivot Table. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *