Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a date of birth column called DOB and I am using the following calculation to get the age:
CustAge = DATEDIFF([DOB], TODAY(), YEAR)
However, in some cases it is giving me the wrong answer. For example for someone born on 30/12/1999 the age gets returned as 18 where obviously it should be 17 as todays date is 09/09/2017.
Does anyone know why I am getting the wrong answer or if there is a better alternative calculation?
Thanks
Solved! Go to Solution.
Hey,
from my point of view this thread provides the best solution:
https://community.powerbi.com/t5/Desktop/Age-and-Age-intervals-3-questions/td-p/27293
Regards
Tom
EDIT: ignore this. I see this same method was within the thread posted as a solution.
Can't you access Power Query? there is a function on the ribbon to calculate age. Duplicate the birthday column and then:
Proud to be a Super User!
Paul on Linkedin.
Thank you Chris for your useful answer.
Actually, many of my visuals already use the field I created by means of the 8 digits algorithm.
I would have liked :
→ your Age/Durations functions in PB Desktop too ;
→ The same behaviour for DAX DateDiff function thant the Excel DateDif function.
Thanks though for your responsiveness 🙂 !
Re: "→ your Age/Durations functions in PB Desktop too"
The Age/duration functions shown are from PBI Desktop (just launch Power Query)
Proud to be a Super User!
Paul on Linkedin.
To calculate the real age at a given date, the DateDiff DAX function returns :
→ not the real age at a given date (like DateDif Excel function)
but
→ the date at the end of year of the given date (contrary to DateDif Excel function)
Consequently, the DAX function is not appropriate.
Personally, I used the following algorithm :
The idea is to temporarily convert respectively year, month and day of the date on 8 digits.
There is an even easier way, just go to the power query, click on the "Transoform" tab and at the "Number Column" section there is a function named "Rounding" select the one that is appropriate to you.
Thank you,
Stavros
I use this calculation based on the T-SQL calculation (datediff is not availlable in my DAX version of the )
=(YEAR(TODAY()) - YEAR(Employee[Birthday])) - IF ( OR ( MONTH(Employee[Birthday]) > MONTH(TODAY()) ; AND(MONTH(Employee[Birthday]) = MONTH(TODAY()) ;DAY(Employee[Birthday]) > DAY(TODAY())) ) ;1 ;0 )
Hey,
from my point of view this thread provides the best solution:
https://community.powerbi.com/t5/Desktop/Age-and-Age-intervals-3-questions/td-p/27293
Regards
Tom
Thanks very much for your help. I solved the problem using:
CustAge = FLOOR(DATEDIFF([DOB], TODAY(), DAY) / 365.25, 1)
The solution that suggested using rounding via the Transform tab does not seem to be possible in the current version of Power BI Desktop, i.e. the Transform tab no longer exists.
Close, but still not correct! There is an extra leap year every 100 years, as long as the year is evenly divisible by 400. Year 1900 was NOT a leap year. Year 2000 was a leap year. And there's no reason to use FLOOR if you format the column as a whole number.
Glad to hear that you've solved this problem. You may help accept solution. That way, other community members may also benefit. Your contribution is highly appreciated.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |