Microsoft Power BI Cookbook

Add the date intelligence column via join

1. Develop an M query which creates a surrogate sequential column to be used in Date Intelligence measures:

• Two columns (Calendar Year and Calendar Month Number) are accessed from an existing SQL view and the Table.AddIndexColumn() function is applied to this sorted table to create the sequential column

let Source = AdWorksProd, DateDim = Source{[Schema = "BI",Item = "vDim_FinDate"]}[Data],YearMonthCols = Table.Distinct(Table.SelectColumns(DateDim, {"Calendar Year","Calendar Month Number"})),YearMonthColSort = Table.Sort(YearMonthCols,{{"Calendar Year", Order.Ascending}, {"Calendar Month Number", Order.Ascending}}),YearMonthColIndex = Table.AddIndexColumn(YearMonthColSort, "YearMonthIndex",1,1), ...