Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to Solution.
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.
@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.
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
Hope it helps! 😊
Thanks, Gaurav
@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.
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
Hope it helps! 😊
Thanks, Gaurav
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.
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
@Anonymous
Add the following column for Age:
Age = INT((TODAY() - Table[birthdate])/365.25)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
Just in case, you have an option to calculate age in Power Query if that helps:
Proud to be a Super User!
Paul on Linkedin.
@Anonymous , why are you not using datediff
new column = datediff([birthdate], today(), year)
or
new column = datediff([birthdate], today(), year) +1
@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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |