Create a dynamic banding attribute

The goal of this example is to create an attribute on the Customer dimension table that groups the customer into age ranges to support demographic analysis:

1. Retrieve the current dimension table with the date column to be used for segmentation. The Date of Birth column is the source for this example.

1. Add variables to the let expression to support the comparison between the current system date and the dimension date (Date of Birth).

• DateTime.LocalNow() is used as the source for current date.
• The result of this variable is used for year, month, and day.

1. Use the Table.AddColumn() function to create Year, Month, and Day columns for the customer dimension (see Figure 95 and Figure 96).

• Currently, the equivalent of a DATEDIFF() function with date intervals (Year, Month, Week, and so on), like the ones in T-SQL and DAX languages, is not available in M.
• A Duration.Days() function can be used for day intervals and additional duration functions are available for hour, minute, and second intervals.

• The Int64.Type value is passed to the optional type parameter of Table.AddColumn() to set the new columns as whole numbers.

1. Add an Age column via an if...then expression.

• The Customer Age expression compares the Current Year, Month, and Day variables against the values of the customer columns created in step 3.
• The Age column can then be used to derive the age segmentation column.

1. Add a Segment column via the column computed in step 4.

• The Customer Age Segment expression simply references the Customer Age column created in step 4.

• The new custom columns can be used to support various visualizations.

• The Age Segment and Age columns can be used in a model hierarchy.