A Date dimension table is an essential component in most any data warehouse or reporting database so techniques to generate these tables have been around for a long time. The foundation of a Date dimension table is a table containing one row per contiguous date in a range that includes every possible transaction date or fact record. To make reporting easier, it is common practice to have multiple date dimensions in the semantic model. For example, if sales transaction facts have an Order Date and a Delivery Date, and both are used independently for reporting; there may be an Order Date dimension and a Delivery Date dimension in the model.
A common practice for building the dimension table is to just populate a single Date type column with the sequential date values. After these rows are inserted, date part functions may be used to populate additional columns by referencing the Date value in an expression. Most every language includes, for example, a MONTH() and YEAR() function to convert a date value into these date parts.
I’m hoping that Paul puts together several of these types of post, where he contrasts building something in SQL, M, and DAX so we can see which language helps most where.