UPDATE 2019-10-18 Because of a bug in YEARFRAC function described in the original blog post and reported by Dominik Petri (thanks!), there are cases where the previous solution does not work well. Moreoever, YEARFRAC is not that fast, so a faster and safer implementation is the following one (another thank you to Rob Farley who guided me in the right direction reducing code size using QUOTIENT or FLOOR!):
VAR Birthdate = Customer[Birth Date] -- Change this date to get the right birthdate VAR ThisDay = DATE ( 2019, 10, 15 ) VAR IntBirthdate = YEAR ( Birthdate ) * 10000 + MONTH ( Birthdate ) * 100 + DAY ( Birthdate ) VAR IntThisDay = YEAR ( ThisDay ) * 10000 + MONTH ( ThisDay ) * 100 + DAY ( ThisDay ) VAR Age = QUOTIENT ( IntThisDay - IntBirthdate, 10000 ) VAR CheckedAge = DIVIDE ( Age, NOT ISBLANK ( Birthdate ) ) RETURN CheckedAge
If you do not have any blank, you can return the Age variable skipping the CheckedAge variable calculation.
If you have a large number of BLANK values in the Birthdate then you could prefer this other option:
VAR Birthdate = Customer[Birth Date] RETURN IF ( NOT ISBLANK ( Birthdate ), VAR ThisDay = DATE ( 2019, 10, 15 ) VAR IntBirthdate = YEAR ( Birthdate ) * 10000 + MONTH ( Birthdate ) * 100 + DAY ( Birthdate ) VAR IntThisDay = YEAR ( ThisDay ) * 10000 + MONTH ( ThisDay ) * 100 + DAY ( ThisDay ) VAR Age = QUOTIENT ( IntThisDay - IntBirthdate, 10000 ) VAR CheckedAge = DIVIDE ( Age, NOT ISBLANK ( Birthdate ) ) RETURN CheckedAge )
This should help in calculating the exact age based on birthdate!
The bug found by Dominik Petri is the following:
-- The following expression should return 19 and it returns 18 instead -- because YEARFRAC returns a decimal number slightly smaller then 19. INT ( YEARFRAC ( DATE ( 2000, 10, 16 ), DATE ( 2019, 10, 16 ), 1 ) )
ORIGINAL BLOG POST
By answering to a student question I realized we often used a wrong calculation of customers age in DAX, and the simple solution is to borrow the same technique used in Excel, using INT and YEARFRAC rather than DATEDIFF or other divisions by 365 or 365.25.
The right calculation is:
Age = INT ( YEARFRAC ( Customer[Birthdate], TODAY (), 1 ) )
Simple and effective.
UPDATE 2019-08-25: make sure to use 1 as the last argument for YEARFRAC and read in the comments an explanation about this is required to get the right calculation based on a birthday.
Returns the year fraction representing the number of whole days between start_date and end_date.
YEARFRAC ( <StartDate>, <EndDate> [, <Basis>] )
Returns the integer portion of a division.
QUOTIENT ( <Numerator>, <Denominator> )
Rounds a number down, toward zero, to the nearest multiple of significance.
FLOOR ( <Number>, <Significance> )
Returns a blank.
BLANK ( )
Rounds a number down to the nearest integer.
INT ( <Number> )
Returns the number of units (unit specified in Interval) between the input two dates.
DATEDIFF ( <Date1>, <Date2>, <Interval> )