[greggyb]

I never see discussion of fiscal calendars in these sorts of threads and articles. Whenever I read these, all I see is something that would fall apart as soon as approximately half of my clients look at it.

We have found that it is more annoying to keep track of the behavior of the library and of a home-grown date dimension. In my organization, we tend to use a standardized pattern that can handle arbitrary calendars, even when we're dealing with standard calendars.

[solidsnack9000]

Do you guys use a custom date type, then?

[greggyb]

No. Dates are dates. Everyone can agree when a specific day exists. It's all about grouping.

Dates have attributes that group them together. "Month" is an attribute that you're familiar with. "Fiscal Period" can take on many specific definitions but it is analogous to "Month".

Those two concepts share a lot of properties. They each collect a series of contiguous dates. Each is adjacent to a similar grouping that falls sequentially and the last date that exists in one is one day prior to the first day that exists in the next. Each falls within a larger category like "Year" or "Fiscal Year".

Year+Period forms a composite key for a period. We can also assign a monotonically increasing field that increments by one with each subsequent period. That field allows simple arithmetic to shift forward and backward. We typically call this attribute PeriodIndex or PeriodSequential. I'll abbreviate to PI here.

If you have a reference PI, you can always find the immediate prior period by subtracting one from the reference. We can assign these for any grain of time period. We typically see Week, Period, Quarter, Semester, Year.

This is the baseline of how we handle dates. There are plenty of utility fields we'll maintain for specific time-based needs, but it's all sugar on top of that.