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 includes special consideration for both calendar and fiscal year, as well a the ability to determine holidays and treatment of leap years. Ultimately I will re-write (and post) this table using DAX in Power BI in order to take advantage of dynamic date handling. This Excel table is formula driven, so it should be fairly simple to download then adjust to your business needs.
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 Power BI.
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