Creating Custom Fiscal Calendars

About This Project

Developed in Power Query using M code, this template creates custom fiscal calendars. It allows for the selection of start and end dates, between a 52-week or a 53-week year, and either 4-4-5, 4-5-4, or 5-4-4 quarterly structure. The resultant Calendar is design to simplify the use of week-based DAX calculations, such as year-to-date, same period last year, and percentage growth using a week granularity.

Project Description

It is common for businesses to use a fiscal calendar instead of a standard Gregorian calendar. When performing business intelligence analysis, a fiscal calendar standardizes every period within the year (quarter, month) as a set of complete weeks, and these fiscal periods always start on the same day of the week. This allows for easier and more accurrate like for like comparisons using week-based calculations.

The template is inspired by the work of Ruth Pozuelo at Curbal – whom further references blog posts by Ken Puls and Matt Allington. The design and configuration of the calendars columns follow the requirements needed to use Marco Russo’s and Alberto Ferrari’s week-based DAX patterns.

The template was developed within Excel Power Query utilizing field parameters. The user can choose the start date and end date, select between a 52-week or a 53-week year, and either 4-4-5, 4-5-4, or 5-4-4 quarterly structure. The table is expanded from the generated list of dates, through a series of applied steps in M code. The result is a fiscal calendar with columns configured in the data model as follows:

  • Date: Date, m/dd/yyyy (8/14/2007), used as a column to mark as date table, which is optional
  • Sequential Day Number: Whole Number, (40040) , same value of Date as integer
  • Fiscal Year: Text (FY 2007)
  • Fiscal Year Number: Whole Number (2007)
  • Fiscal Quarter: Text (FQ3)
  • Fiscal Quarter Number: Whole Number (3)
  • Fiscal Year Quarter: Text (FQ3-2007)
  • Fiscal Year Quarter Number: Whole Number (8030)
  • Fiscal Week: Text (FW33)
  • Fiscal Week Number: Whole Number (33)
  • Fiscal Year Week: Text (FW33-2007)
  • Fiscal Year Week Number: Whole Number (5564)
  • Fiscal Month: Text (FM Aug)
  • Fiscal Month Number: Whole Number (8)
  • Fiscal Year Month: Text (FM Aug 2007)
  • Fiscal Year Month Number: Whole Number (24091)
  • Day of Fiscal Month Number: Whole Number (17)
  • Day of Fiscal Quarter Number: Whole Number (45)
  • Day of Fiscal Year Number: Whole Number (227)
  • Day of Week: ddd (Tue)
  • Day of Week Number: Whole Number (6)
  • Weekday: Text (Weekday)

Source: https://www.daxpatterns.com/week-related-calculations/

The columns are designed to simplify the week-based DAX formulas. For example, the Day of Fiscal Year Number column contains the number of days since the beginning of the fiscal year; this number makes it easier to find a corresponding range of dates in the previous year.

This template allows the user to easily create custom Fiscal Calendars that can be loaded into a data model.

Portfolio