There’s a great deal of overlap among these techniques. I list all the ones I’ve stumbled upon for completeness. The entries are in no particular order.
But first, why? The answer from Reza Rad at RADACAD
T-SQL Methods
- Creating a date dimension or calendar table in SQL Server by Aaron Bertrand at SQLServerCentral (Recursive CTEs, Functions, Subqueries; lots of elements)
- Create an Extended Date Dimension for a SQL Server Data Warehouse By Dinesh Asanka at MSSQLTips
- Script to Generate and Populate Date Dimension Version 2: Adding Multiple Financial Years by Reza Rad at RADACAD
- Here’s a GitHub script from Chad Gibson / HBennet, “CreateDateDimensionWithFiscals.sql“
- Create and Populate Date Dimension for Data Warehouse by Mubin M. Shaikh at CodeProject
- Create and fill Time dimension by Joost van Rossum (SSIS blog)
- Mastering Dimensions of Time by PhyData DBA at SQLServerCentral
- Populating a Kimball Date Dimension by ArcaneCode
- Don McMunn’s Freeware “Date Dimension Tool Kit“
- How to create a time dimension in SQL Server by SQL Master
- A quick and dirty date dimension for PowerPivot by Bill Fellows
DAX Methods
- All in One: Script to Create Calendar Table or Date Dimension using DAX in Power BI by Reza Rad at RADACAD (he also has a PQ version)
- Creating a Date Dimension Table in Power BI by Nat Sundar at MSSQLTips
- Creating a DAX Calendar (multiple methods) by PowerBI.Tips
- Reference Date Table in DAX and Power BI by Marco Russo at SQLBI
- Bravo from SQLBI has a “Manage Dates” feature
Power Query Methods
- Creating a Date Dimension with Power Query by Brian Knight at SQLServerCentral
- All in One: Script to Create Date Dimension in Power BI using Power Query by Reza Rad at RADACAD
- Create Calendar Table Using Power Query M Language by Kenneth Omorodion at MSSQLTips
- Creating a Dynamic Calendar Date Range in Power Query {Part 1, YouTube video (7:34) by Reid Havens
- Creating Date (Calendar) Hierarchy Columns in Power Query {Part 2} by Reid Havens
SSAS and Time
- Built in Time Dimension and Time Intelligence in SQL Server Analysis Services (multidimensional) by Ray Barley at MSSQLTips
- Creating a Time Dimension in 10 Easy Steps (Using Visual Studio / BIDS / SSDT) by SSW
Other Calendar / Date / Time References
- Power BI Calendar Tables by Matt Allington at Excelerator BI