Ultimate Calendar Table for Power Query

For time-based analyses in Power BI, PowerPivot and SSAS Tabular, you always need a calendar table in the data model. Even more so if you use the DAX Time Intelligence functions. Then the calendar table must meet several criteria. And that’s also why we have here the ultimate calendar table, currently in version 2.06, so that you can use it quickly and easily in your reports. Originally published in 2017 at this webpage, it became quickly popular, and is said to be the most used script in Czech and Slovak Republic. Later, its modifications spread globally. This is the true original.

Download link

The ultimate calendar table looks like this – a preview of the first few rows and columns:

The Ultimate Time Table script is compatible with Power Query in these products:

  1. Power Query for Excel 365 and Excel 2010/2013/2016/2019/2021
  2. Power Query for Excel 365 for Mac
  3. Power BI Desktop (both for cloud service as well as for PBI RS)
  4. Power BI cloud service
  5. Power BI Report Server
  6. Power BI / Power Platform Data Gateway
  7. Power BI Dataflows
  8. Power BI Datamarts
  9. Power Automate
  10. Dataverse Database (Power Apps, MS Teams, MS Dynamics, etc.)
  11. SQL Server Analysis Services (SSAS Tabular)
  12. Azure Analysis Services
  13. SQL Server Integration Services (SSIS) – Power Query Source
  14. Azure Data Factory (ADF)

Script preview:

// Ultimate Calendar Table, v2.06
// downloaded from the www.powerquery.sk website
let
    startYear = 2020,
    endYear = 2024,  // use Date.Year(DateTime.FixedLocalNow()) for current year
    language = "en-US",    // US English = en-US, German = de-DE, Austrian German = de-AT, Slovak = sk-SK, Czech = cs-CZ
    columnNamesWithoutDiacritics = false,   // enter "true", if you want the columns without diacritics (for easier writing of DAX formulas)
    fiscalYearStartMonth = 3,
    fiscalYearPrefix = "FY",
    semesterPrefix = "H",
    quarterPrefix = "Q",
    monthPrefix = "M",
    weekPrefix = "W",
    firstDayOfWeek = Day.Monday,

    startDate = #date(startYear, 1, 1),
    endDate = #date(endYear, 12, 31), 
    timespanDuration = Duration.Days(Duration.From(endDate - startDate)) + 1,
    ...end of script preview...

Download link

The entire script in its most recent version can be downloaded from this link.

Instructions for using the script can be found in this blog post (Slovak only).

News in the 2.06 version: (February 23, 2024)

  • added parameter for the 1st day of week, for correct week computations for different regional settings,
  • added 2 columns with month decade,
  • added flagging columns, if the date is in current week, ISO week, month, quarter, semester, year, ISO year,
  • simplified the step of renaming columns to selected language.

Previous versions: (Slovak only)
available at this website