Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
hello,
I am trying to create a column which calculates the age of the given person. i followed the below procedure
1. create " new column"
u can see the formula in the image below.
as you can see it gives me the above error , even when i move the today() function in the beginning. so instead i inserted today's date. it does calculate but it gives really weird calculations. pls see below
any help will be appreciated
Solved! Go to Solution.
age = IF(ISBLANK(Users[DateOfBirthday]),BLANK(),Year(today())- Year(Users[DateOfBirthday]))
I create two columns:
HasHadBirthday = OR(( Month(MyTable[DateOfBirth])==MONTH(MyTable[CheckDate]))*(DAY(MyTable[DateOfBirth])<=DAY(MyTable[CheckDate])),(MONTH(MyTable[DateOfBirth])<MONTH(MyTable[CheckDate])))Computing the age is done with the check date year, minus the birth day year. If the person hasn't yet had their birthday in the check year, subtract 1.
AgeOnCheckDate = YEAR(MyTable[CheckDate]) - YEAR(MyTable[DateOfBirth]) - IF(MyTable[HasHadBirthday],0,1)
The correct, accurate formula is:
@Eric_Zhang : Thank you for the solution.It was really elegant. another question. some date of birth fields are blank and the value returned is 2017. Which conditional statement to use so that when 2017 is returned it inputs a null value in the field.?
age = IF(ISBLANK(Users[DateOfBirthday]),BLANK(),Year(today())- Year(Users[DateOfBirthday]))
@mayankkapoor85 @Eric_Zhang This would actually give the wroung answer where the day and month of the persons birth has not yet happened in the current year.
So what you should try is
INT(YEARFRAC(Ep[Date],TODAY()))
CAPE
This works great!
@CAPEconsulting dividing by 365.25 is not really a good idea either.
A correct Power Query solution can be found in this topic.
Basically the 2 dates are converted to a number with yyyyMMdd, subtracted and integer-divided by 10,000
Example: 20,171,006 - 19,620,421 = 550,585 => 55.0585
So you don't agree with a correct solution? Strange.
Then you might as well disagree with the approach to add 2 numbers as number1 + number2.
This is just black and white mathematics, which is either correct or wrong. Nothing to (dis)agree.
There will be other correct solutions people may prefer, though.
Example: your solution Oct 6, 2016 to Oct 6, 2017 returns 0 instead of 1.
Oh goodness. Its not the maths that I am talking about. I know the arithmentic here quite well. I mean the DAX vs M code. String and especially date manipulation in M code is far more tedious. Manipulation of and calculations in DAX are my prefernce. So that'ss what I am trying to say. No need to get nasty please.
Apologies, I didn't know it was a DAX/M discussion.
Why not use DATEDIFF with YEAR then: Age = DATEDIFF(Query1[DOB], Query1[Date],YEAR)
(DAX is not my specialism, but this looks to me like the better solution).
The DATEDIFF method is wrong, as DATEDIFF .. YEAR counts the number of year boundaries between the two dates. i.e. the number of midnights on 1st January.
Please see my other post for correct formula.
This will give a wrong answer when the month and day of the persons birth have not commenced yet in the current year. Example if date of birth of 6th Jul 2007 and if today is 6th Oct 2017 the person's age is 9 years but your DAX formula will return 10 years.
Using what I had suggested initially i.e. INT(YEARFRAC(Ep[Date],TODAY())) will retun 9 years in the above example.
Also I know that my solution will have to wait for the entire day of birth to get over in any calendar year to give the right answer. What I mean is that if DoB was 6/10/2016 and today is 6/10/2017 then yes it will give 0. And the moment today's date turns to 7/10/2017 it will give the right answer i.e 1. Now in most of my models this is what I want, but of course if someone wants the age to be 1 on the date of birth they could always subtract 1 from DoB or add 1 to TODAY within the DAX. Their choice there....
Your solution: agetest = FLOOR(DATEDIFF(date(2017,02,05), date(2020,02,05), DAY) / 365.25, 1) is flawed. It results in age 2, but should be 3 on their birthday. There are no leap years yet it adjusts for one.
This is a direct solution that works:
age = IF(
format([birthdate],"MMDD") <= FORMAT(TODAY(),"MMDD"),
DATEDIFF([birthdate],TODAY(), YEAR),
DATEDIFF([birthdate],TODAY(), YEAR) - 1)
In that case you can also translate my solution to DAX:
Age = ROUNDDOWN(((10000*Year(Query1[Date])+100*Month(Query1[Date]) + Day(Query1[Date]))-(10000*year(Query1[DOB])+100*MONTH(Query1[DOB])+Day(Query1[DOB])))/10000,0)
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
107 | |
99 | |
99 | |
38 | |
37 |
User | Count |
---|---|
157 | |
121 | |
73 | |
73 | |
63 |