In this example, the goal is to add a column to the customer dimension table that groups the customers into four categories based on the date of their first purchase. Specifically, the new column needs to leverage the existing first purchase date column and assign the customer rows to one of the following four categories—First Year Customer, Second Year Customer, Third Year Customer, Legacy Customer. Since the column will be computed daily with each scheduled refresh, it will be used by the sales and marketing teams to focus their efforts on new and older customer segments.
A combination of date functions and conditional logic (if..then..else) is used with the Table.AddColumn() function to produce the new column:
let
// Customer History Date Bands
CurrentDate = DateTime.Date(DateTime.LocalNow()),
OneYearAgo = Date.AddYears(CurrentDate,-1),
TwoYearsAgo = Date.AddYears(CurrentDate,-2),
ThreeYearsAgo = Date.AddYears(CurrentDate,-3),
//Customer Dimension
Source = AdWorksSQLServer,
Customer = Source{[Schema = "BI", Item = "vDim_Customer"]}[Data],
CustomerHistoryColumn = Table.AddColumn(Customer, "Customer History Segment",
each
if [Customer First Purchase Date] >= OneYearAgo then "First Year Customer"
else if [Customer First Purchase Date] >= TwoYearsAgo and [Customer First Purchase Date] < OneYearAgo then "Second Year Customer"
else if [Customer First Purchase Date] >= ThreeYearsAgo and [Customer First Purchase Date] < TwoYearsAgo then "Third Year Customer"
else "Legacy Customer", type text)
in
CustomerHistoryColumn
As shown in the following image from the Power Query Editor, the Customer History Segment produces one of four text values based on the Customer First Purchase Date column:
In this example, the customer Alan Zheng falls into the Third Year Customer segment since his first purchase date (10/20/2014) is after 10/18/2014 - three years prior to the current date (10/18/2017). When the dataset is refreshed on 10/21/2017, Alan Zheng will be re-classified as a Legacy Customer by the Customer History Segment column since his first purchase date will be more than three years old at that time.
Like the previous M query example of a trailing three year filter, the conditional logic for the derived customer column is also translated into T-SQL via query folding:
