Power Query: Reusable Calendar function

This Power Query function dynamically generates a calendar table based on a given start date, end date, and fiscal year start month. It is designed to support time-based analysis in Power BI by including key date attributes, fiscal calculations, and various helper columns.

Power Query Calendar Function diagram illustrating parameter inputs for start date, end date, and fiscal start month, with outputs like date, fiscal year, ISO week, and day classifications such as weekend, workday, and today.

Function Parameters

The function accepts three input parameters:

• Start Date: The first date to include in the calendar.

• End Date: The last date to include.

• Fiscal Year Start Month: Defines when the fiscal year begins (e.g., 7 for July).
  

1. Date Generation

The function creates a continuous list of dates from the start to the end date. Each date is then transformed into a structured table with different time-related attributes.

2. Basic Date Attributes

These columns extract key date components, such as:

• Year and Short Year (e.g., 2024 and 24)

• Quarter and Month (numeric and text formats)

• Day of the Month and Week

• Week Number (standard and ISO formats)

• Day of the Year for tracking position within the year
  

3. Fiscal Year and Quarter

To support custom fiscal periods, the function calculates:

• Fiscal Year (e.g., FY2025 if the fiscal year starts in July)

• Fiscal Quarter (e.g., FQ1, FQ2, etc.), adjusted for the fiscal start month.
  

4. First and Last Days of Periods

For easier time-based calculations, the function adds:

• First and Last Day of the Month

• First and Last Day of the Quarter

• First and Last Day of the Year
  

5. Weekday and Workday Identification

To differentiate weekdays from weekends, the function includes:

• Day Name (e.g., Monday, Tuesday)

• Is Weekend (Boolean flag: TRUE for Saturday/Sunday)

• Is Workday (Boolean flag: TRUE for Monday–Friday)
  

6. Sorting-Friendly Numeric Columns

To ensure proper chronological sorting, numeric representations of date periods are included:

• Date Integer (YYYYMMDD) for unique row identification

• Month-Year Numeric Format (YYYYMM) for ordering months correctly

• Quarter-Year Numeric Format (YYYYQQ) for quarter-based sorting
  

7. Dynamic Flags

For enhanced usability, the function detects:

• Today's Date (IsToday) for dynamic comparisons

• Week Ending Date (useful for weekly reporting)
  

Conclusion

This function automates the creation of a comprehensive date table, making it easy to use in Power BI for time intelligence calculations, sorting, and filtering. With its fiscal adjustments, ISO weeks, and dynamic flags, it supports advanced reporting scenarios efficiently. Here is a code snippet so you can directly use it in Excel or Power BI:

Need a code and instructions? Free of charge =)

PQ_M_calendar_advanced.txt

Download TXT • 5KB

---

Do you need an advanced calendar in Power Query that is ready to use in Excel and Power BI? Check it out.

Advanced Calendar (reusable function)

€5.00

Buy Now