cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Gguliani_0-1621509395821.png

 

2 ACCEPTED SOLUTIONS
v-yalanwu-msft
Community Support
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:

v-yalanwu-msft_0-1621845566636.jpeg

 

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:

v-yalanwu-msft_1-1621845566638.png

 

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.

View solution in original post

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

Gguliani_0-1621935284867.png

2) Using DATEDIFF, FLOOR & 365.25

Gguliani_2-1621935351961.png

3) Using ROUNDDOWN, Today & 365.25

Gguliani_3-1621935434546.png

 

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

Gguliani_4-1621935654085.png

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.

Gguliani_0-1621938803554.png

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.
I referred this link by someone- if you want to learn more- https://blog.magnetismsolutions.com/blog/colinmaitland/2019/10/07/how-to-accurately-calculate-ages-i...
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

Gguliani_0-1621941006744.png

 

Hope it helps! 😊

Thanks, Gaurav

View solution in original post

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

Gguliani_0-1621935284867.png

2) Using DATEDIFF, FLOOR & 365.25

Gguliani_2-1621935351961.png

3) Using ROUNDDOWN, Today & 365.25

Gguliani_3-1621935434546.png

 

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

Gguliani_4-1621935654085.png

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.

Gguliani_0-1621938803554.png

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.
I referred this link by someone- if you want to learn more- https://blog.magnetismsolutions.com/blog/colinmaitland/2019/10/07/how-to-accurately-calculate-ages-i...
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

Gguliani_0-1621941006744.png

 

Hope it helps! 😊

Thanks, Gaurav

v-yalanwu-msft
Community Support
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:

v-yalanwu-msft_0-1621845566636.jpeg

 

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:

v-yalanwu-msft_1-1621845566638.png

 

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

@Fowmy , @PaulDBrown 

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

Fowmy
Super User
Super User

@Anonymous 

Add the following column for Age:

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

Fowmy_0-1621511851211.png

 

 

 

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

PaulDBrown
Super User
Super User

@Anonymous 

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

Age.jpg

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






amitchandak
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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.