Microsoft Power BI Cookbook

Date dimension M Query

1. Create an M query that accesses an existing SQL date table view, retrieves the last three years of dates, and computes 11 additional columns via M functions:
   • A filter is applied via Table.SelectRows() to only retrieve the last three years of dates, and conditional logic is used to populate dynamic Year Status and Month Status columns (for example, Current Year):

 let Source = AdWorksProd, Dates = Source{[Schema = "BI", Item = "vDim_FinDate"]}[Data], CurrentDate = DateTime.Date(DateTime.LocalNow()), CurrentYear = Date.Year(DateTime.Date(DateTime.LocalNow())),  CurrentMonth = Date.Month(DateTime.Date(DateTime.LocalNow())),  FilteredDates = Table.SelectRows(Dates, each [Date] >= Date.AddYears(CurrentDate,-3) and [Date] ...