A date table is essential to data work, whether you’re working in SQL, Tabular Modeling for Power Pivot, Power BI or SSAS.
Because if you work with any kind of data, and particularly accounting data, you’ll always have to ask for the appropriate time period.
You can find lots of good examples of how to build a data table, both in SQL and in Power BI, if you operate on a calendar year or your fiscal months are calendar months.
Google is your friend here, so if the links above don’t help, you can find others.
But, if you’re a retailer or another business that operates on some variety of a 4-5-4 calendar (with a 53rd week showing up every couple of years), that’s not as easy to find.
This is something I do all the time, so I’ve developed SQL code to create the table in whatever data warehouse or reporting database I’m using.
This SQL works specifically with the NAV accounting period table. I’ve placed the code in my GitHub repository with instructions if you want to use it.
This table, by the way, is designed with eventual tabular modeling in mind. To illustrate, I’m going to look at an example with sample data for a week right around year end — and explain some “special” features.
Here, we’re looking at the end of 2016/beginning of 2017:
A few comments:
- I’m using DateTime for my posting date
In 99 percent of my reporting, time is never entered with the date time and NAV. But all NAV fields for date information are defined as DATETIME.
My program only creates one entry per posting date.
This works with some exceptions including:
- Year-end roll forward of retained earnings. Every year, my program posts an entry on the last second of the fiscal year to move income statement amounts to retained earnings. It’s quite rare that I need to include this amount in my reports, and I’ve found it easier to handle that exception separately.
- Change Log Entries. Are all based on DateTime.*
Creating a date time table isn’t really feasible. (You want a record for every second of the last year.) In those situations, it’s easier to cast the data in the source table to Date and work from there.
2. Fiscal Quarter, Fiscal Period, Fiscal Week vs. QuarterID, PeriodID, WeekID
Fiscal Quarter, Fiscal Period, and Fiscal Week are pretty obvious. At the end of the year, we are in Quarter 4, Period 12 and Week 52. On the first day of the year, we are in Quarter 1, Period 1 and Week 1.
But what about QuarterID, PeriodID, and WeekID?
These are sequences of numbers starting at the beginning of the calendar. They’re useful when we need to do prior comparisons.
For example, if I’m in the first quarter of FY 2017, I’m in Quarter 1 but Quarter ID 523.
To find the previous quarter, I can just choose QuarterID -1 or 522.
To find the matching quarter in the prior year, I can just QuarterID-4 or 519.
This makes formulas much easier to create in both SQL and Tabular Modeling. You don’t have to worry whether you’re in the first period of the year. It also makes math for last X period logic easier to create.
* It’s actually stranger than that. Change Log Entries has two fields with DATETIME data types. One stores the date and the time of the change. The other stores just the time (with the date defaulting to 1754-01-01).