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 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.
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.
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”)
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.
The Sunday of the FY Start Date’s week is also calculated. That will ensure that the Fiscal Weeks line up correctly.
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
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.
___________________