cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## What's wrong with the AGE calculation using Calculated Columns here?

Hi All,

I am just trying to understand why my formula wouldn't work for birthdates that were in the same month as today i.e. May but only for dates that were more than today's date i.e. > 20th May if we consider today

I am using this:

Age (WithoutRound & 365.25) =
IF(
MONTH(NOW()) > MONTH('Customer Lookup'[birthdate]),
YEAR(NOW()) - YEAR('Customer Lookup'[birthdate]),
IF(
MONTH(NOW()) < MONTH('Customer Lookup'[birthdate]),
YEAR(NOW()) - YEAR('Customer Lookup'[birthdate])-1,
IF(
DAY(NOW()) >= DAY('Customer Lookup'[birthdate]),
YEAR(NOW()) - YEAR('Customer Lookup'[birthdate]),
YEAR(NOW()) - YEAR('Customer Lookup'[birthdate]-1)
)
)
)

Why wrong age in the column with cross as shown below, where i have the formula shown above

2 ACCEPTED SOLUTIONS
Community Support

Hi @Anonymous  ,

According to you description，The ")" in your measure is not in the right position, it should be placed in front of -1,  and the result is correct after changing the position：

Modify the measure：

``````Age (WithoutRound & 365.25) =
IF (
MONTH ( NOW () ) > MONTH ( 'Customer Lookup'[birthdate] ),
YEAR ( NOW () ) - YEAR ( 'Customer Lookup'[birthdate] ),
IF (
MONTH ( NOW () ) < MONTH ( 'Customer Lookup'[birthdate] ),
YEAR ( NOW () ) - YEAR ( 'Customer Lookup'[birthdate] ) - 1,
IF (
DAY ( NOW () ) >= DAY ( 'Customer Lookup'[birthdate] ),
YEAR ( NOW () ) - YEAR ( 'Customer Lookup'[birthdate] ),
YEAR ( NOW () ) - YEAR ( 'Customer Lookup'[birthdate] ) - 1
)
)
)
``````

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

@v-yalanwu-msft Awesome
I learnt from your post on this query, and tagging you all @Fowmy, @PaulDBrown, @amitchandak - so that I can share my findings:-
Of the 3 ways (shown below) I used to solve this using Calculated columns, only Option 1 gave results in the way we celebrate birthdays. Meaning, a person celebrates birthday when the same day comes every year and not considering Feb 29th as birthday.

1) Using Logical IF with Day, Month, Year, Now function

2) Using DATEDIFF, FLOOR & 365.25

3) Using ROUNDDOWN, Today & 365.25

I say Option 1 is correct, because due to the approximation brought by 365.25, I noticed an anomaly with option 2 & option 3 in Age as highlighted below if someone's birthday is today

The reason they are incorrect is because the birthday when falls today and birthyear being a leapyear- these formulas don't adjust the approximation brought forward by 365.25.

Even the Power Query method suggested above by @PaulDBrown isn't accurate because Age when rounded off to Whole Number doesn't Round Down, but even if we Round Down, the Age Calculation M-language formula should be changed in Power Query from 365 to 365.25.

Then, the Power Query method would match what @Fowmy  & @amitchandak suggested but even those are not 100% correct if your birthday falls on a leap year and it is today.
What i realized after going through this link & YEARFRAC function is that our methods may be correct in our own way but not technically accurate- not even the one suggested by YEARFRAC with either basis as 1 (actual) or 3 (365)
But, if we use 365.2422 instead of 365.25, all our values will match with different methods and may be most accurate with no anomalies.
See, how values matched for all cases after that

Hope it helps! 😊

Thanks, Gaurav

7 REPLIES 7
Anonymous
Not applicable

@v-yalanwu-msft Awesome
I learnt from your post on this query, and tagging you all @Fowmy, @PaulDBrown, @amitchandak - so that I can share my findings:-
Of the 3 ways (shown below) I used to solve this using Calculated columns, only Option 1 gave results in the way we celebrate birthdays. Meaning, a person celebrates birthday when the same day comes every year and not considering Feb 29th as birthday.

1) Using Logical IF with Day, Month, Year, Now function

2) Using DATEDIFF, FLOOR & 365.25

3) Using ROUNDDOWN, Today & 365.25

I say Option 1 is correct, because due to the approximation brought by 365.25, I noticed an anomaly with option 2 & option 3 in Age as highlighted below if someone's birthday is today

The reason they are incorrect is because the birthday when falls today and birthyear being a leapyear- these formulas don't adjust the approximation brought forward by 365.25.

Even the Power Query method suggested above by @PaulDBrown isn't accurate because Age when rounded off to Whole Number doesn't Round Down, but even if we Round Down, the Age Calculation M-language formula should be changed in Power Query from 365 to 365.25.

Then, the Power Query method would match what @Fowmy  & @amitchandak suggested but even those are not 100% correct if your birthday falls on a leap year and it is today.
What i realized after going through this link & YEARFRAC function is that our methods may be correct in our own way but not technically accurate- not even the one suggested by YEARFRAC with either basis as 1 (actual) or 3 (365)
But, if we use 365.2422 instead of 365.25, all our values will match with different methods and may be most accurate with no anomalies.
See, how values matched for all cases after that

Hope it helps! 😊

Thanks, Gaurav

Community Support

Hi @Anonymous  ,

According to you description，The ")" in your measure is not in the right position, it should be placed in front of -1,  and the result is correct after changing the position：

Modify the measure：

``````Age (WithoutRound & 365.25) =
IF (
MONTH ( NOW () ) > MONTH ( 'Customer Lookup'[birthdate] ),
YEAR ( NOW () ) - YEAR ( 'Customer Lookup'[birthdate] ),
IF (
MONTH ( NOW () ) < MONTH ( 'Customer Lookup'[birthdate] ),
YEAR ( NOW () ) - YEAR ( 'Customer Lookup'[birthdate] ) - 1,
IF (
DAY ( NOW () ) >= DAY ( 'Customer Lookup'[birthdate] ),
YEAR ( NOW () ) - YEAR ( 'Customer Lookup'[birthdate] ),
YEAR ( NOW () ) - YEAR ( 'Customer Lookup'[birthdate] ) - 1
)
)
)
``````

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Thanks for showing me other ways, I am also interested in understanding why my approach didn't work.

It just didn't work for today's month and for birth dates greater than today. Please suggest

Super User

@Anonymous

Add the following column for Age:

``Age = INT((TODAY() - Table[birthdate])/365.25)``

Did I answer your question? Mark my post as a solution! and hit thumbs up
Super User

@Anonymous

Just in case, you have an option to calculate age in Power Query if that helps:

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Super User

@Anonymous , why are you not using datediff

new column = datediff([birthdate], today(), year)

or

new column = datediff([birthdate], today(), year) +1

Anonymous
Not applicable

@amitchandak That DATEDIFF thing worked for me as well, check the green tick column on screenshot above.

I wanted to know why my approach didn't work since i was unaware of DATEDIFF when i tried that.

Thought there would be a reason as to why that formula didn't work just for May months where dates were greater than today's date

Announcements