Microsoft Power BI Cookbook

Dynamic date parameter query

1. In the Query Editor, create a new blank query and name it WeekEndDatesParamList.

2. Reference the existing date dimension query and use standard M functions to select the week ending date column and the dynamic Calendar Month Status column described in Chapter 5, Creating Power BI Dashboards and Chapter 6, Getting Serious with Date Intelligence:

let DateColSelect = Table.SelectColumns(Date,{"Calendar Month Status","Calendar Week Ending Date"}),DateFilter = Table.SelectRows(DateColSelect, each [Calendar Month Status] = "Current Calendar Month" or [Calendar Month Status] = "Prior Calendar Month"),ListOfDates = List.Distinct(DateFilter[Calendar Week Ending Date])in ListOfDates

• The List.Distinct() function is necessary, ...