Building queries based on calendar periods is pretty straight forward, but when challenged to build a report using fiscal periods, the Analytics Edge cell references can to be used to get any fiscal period you need. The magic is figuring out a series of Excel formulas (or just manually entering them) for the fiscal dates.
Use An Anchor Date
Reporting almost always references the last fiscal period, sometimes compared to the previous fiscal period and/or the same fiscal period in the previous year. The worse case is usually fiscal weeks, which can start and stop on odd days of the week, like Saturday through Friday.
If you can figure out one anchor date, everything else is easy: weeks are always 7 days long, with a typical 52 weeks in a fiscal year. The best ‘anchor date’ to focus on is the end of the last fiscal period, because the rest are easy from there (-6 days for the start of the period, -7 days to the end of the previous week, -(52*7) days to the end of the same fiscal week in the previous year).
Although you could manually enter the anchor date, it is much easier for Excel to calculate it for you — then all you need to do is refresh the queries and your report is updated. One less thing to worry about.
Constructing The Excel Formulas
An Excel formula can be use to get the anchor date. There are two Excel functions of use here: TODAY(), which gets today’s date, and the WEEKDAY(), which gets today’s day of the week (where 1 is for Sunday through 7 for Saturday).
For the anchor date, use the formula below, entering the WEEKDAY() value for the weekday of the end of your fiscal week. This formula accounts for the end of fiscal week being before or after today in the current calendar week.
=TODAY-(WEEKDAY(TODAY()+7-<weekday number of end of fiscal week>))
e.g. for a fiscal week ending on Friday (WEEKDAY() value of 6), use:
=TODAY-(WEEKDAY(TODAY()+7-6))
Then you can subtract days to get the dates of your other fiscal periods’ start and end dates. The full set of formulas would look like this:
Using Cell References In Queries
If you are using Analytic Edge to automate your marketing reports, the connectors allow you to use cell references for the start and end dates. In this way, you can easily run queries for the period as well as for the previous period and the equivalent period in the previous year.
