In some BI environments, it’s not feasible to alter the source date table per the previous recipe or even modify the SQL view used to load the date dimension table; at least, not in the short term. In these situations, Power BI Desktop's Query Editor and M expressions can serve as an effective alternative to deliver the same columns necessary to drive robust date intelligence analysis.
In this recipe, an example date dimension M query is shared, which builds common date attributes as well as dynamic logical columns. Additionally, a process for adding sequential date intelligence columns via M expressions is also included.