An interesting dimension to report on for demographics is the age of customers. Understanding what type of customers buy what types of products can help inform our customer insight that can be used for targeting marketing offers more effectively.
In the DimCustomer table of the Contoso Retail Sales DW, we have a field called: Birthdate, but of course, this is unique for all the customers. What we want to do is group these ages into buckets or age bands, so we can create a more informative report. We can then use the grouping in graphs or as filters as in the graph below:
To create the categorization, I created a new calculated column in the DimCustomer table with the DAX code following the steps outlined below. Note the exact categories you use are up to you or the business. There are no definitive rules on what age bands you should follow, but by studying other reports, you will get the gist of the most common methods.
The first best practice step is to start creating variables.
The _dob variable is assigned the [BirthDate] field.
The _now variable is assigned the date of today, using the TODAY() function.
The _ageadys variable is then assigned the difference in the number of days between the date of birth field and today’s date, which is essential for the persons’ age in days.
The _ageyears variable divides the age in days by 365, to get the age in years.
The section after the RETURN statement then returns the age band category, based on the IF ELSE logic.
It’s important to put the statements in the correct order, starting from the least upwards, and note that there are some blank Birthdates if have used IF(_ageyears = BLANK(), “None”.
I only knew there were some blank dates by visually inspecting the BirthDate columns. If I hadn’t used this the blanks would have been picked up as “<18”), so it’s important to sense check as much as possible.
DAX for Calculated Column (note Age Range is the column name):
Age Range =
VAR _dob = DimCustomer[BirthDate]
VAR _now = TODAY()
VAR _agedays = DATEDIFF(_dob, _now, DAY)
VAR _ageyears = _agedays/365
RETURN
IF(_ageyears = BLANK(), “None”,
IF(_ageyears<18, “<18”,
IF(_ageyears<30, “18 to 29”,
IF(_ageyears<40, “30 to 39”,
IF(_ageyears<50, “40 to 49”,
IF(_ageyears<60, “50 to 59”,
IF(_ageyears<70, “60 to 69”,
IF(_ageyears<80, “70 to 79”,
IF(_ageyears>=80, “80 plus”)))))))))
Leave a Reply