Tracks Calendar and Fiscal years by day, as well as custom holidays and workdays.
I am sharing a custom time intelligence Calendar Table in Excel that I built for our custom calendars needs. It considers calendar and fiscal year, as well as holidays and adjustments for leap years.
When opening the table, you’ll see the calendar lists every date between January 1, 2015 through December 31, 2025, including various date attributes in separate columns.
External tables in Excel allow you to maintain a highly customized calendar table that aid in writing customer time intelligence calculations in DAX.
Calendar Table Includes:
- “Name Columns” that provide for reporting labels such as Day Name, Month Names, and Quarter Name labels.
2. “Number Columns” that provide for specific Month (1-12), Week in year, Day in year (1-365), Day in week (1-7), and Quarter numbers (1-4).
3. “Sequence Columns” that provide for a continuous number sequence that extends over the entire table. All numbers in these columns are continuous and help when writing custom time intelligence functions that trace period over period changes.
4. Leap years with February 29, are folded into the prior day (February 28) when accounting for Week and Day numbers (and sequence numbers) to simplify year over year comparisons on leap years, and to maintain a consistent 365 day calendar.
5. A Holiday table off to the right of the calendar table lets you list holidays by date, and indicate with a 1 or 0 if your table should recognize (1) or ignore (0) a holiday within the table.
6. Finally, there is a workday continuous sequence number column that omits weekends and selected holidays for other workday time intelligence calculations.
As always, thanks for visiting! – Mark
Leave a Reply