Age calculation
Next, you are going to determine the age of each customer. The Customer table currently contains a column with the birth date for each customer. This column, along with the TODAY function and some DAX, will allow you to determine each customer's age. Your first attempt at this calculation may be to use the DATEDIFF function in a calculation that looks something like the following:
Customer Age = DATEDIFF('Customer'[Birth Date], TODAY(), YEAR)
The TODAY function returns the current date and time. The DATEDIFF function returns the count of the specified interval between two dates; however, it does not look at the day and month, and therefore does not always return the correct age for each customer.
Let's rewrite the previous DAX formula in a different way. In this example, you are going to learn how to use conditional logic and the FORMAT function to return the proper customer age. Please keep in mind, that there are many ways to perform this calculation.
Select the Customer Age column from the previous step and rewrite the formula to look like the following:
When completed, the preceding code returns the correct age for each customer. The FORMAT function is used to return the two-digit month and two-digit day for each date (the birth date and today's date). Following the logical test portion of the IF statement are two expressions. The first expression is triggered if the logical test evaluates to true, and the second expression is triggered if the result of the test is false. Therefore, if the customer's month and day combo is less than or equal to today's month and day, then their birthday has already occurred this year, and the logical test will evaluate to true, which will trigger the first expression. If the customer's birthday has not yet occurred this year, then the second expression will execute.