Forum Discussion
Issues with rounding on date calculations and rounding down function
Hello,
I hope some can help.
I have some calculation that calculate a persons age based on two columns. I create a custom column with the following measure:
[Report Date] - [Birthdate]
This gives a column with the number of days between - this figure produced looks correct. I then add another column changing the days to years using the Time function to change the days to years - this then gives and incorrect figure because it simply divides the days by 365 not taking into account leap years etc.
For example
[30/04/2022] - [13/05/1962] = 21902 days. His age on 30/04/2022 should be 59 but the days to years does the following:
21902/365 = 60.005
Any help getting a more accurate figure would be much appreciated.
Thanks,
Bfaws
- Anonymous3 years ago
use this dax formula :
Age DateDiff = INT ( YEARFRAC ( DATE(2022,4,30), DATE(1962,05,13), 1 ) ) it will gives you 59andthis formula :Age DateDiff = DATEDIFF(DATE(2022,4,30),DATE(1962,05,13),YEAR) gives 60Hope that will help you 😄
3 Replies
- AnonymousNot applicable
use this dax formula :
Age DateDiff = INT ( YEARFRAC ( DATE(2022,4,30), DATE(1962,05,13), 1 ) ) it will gives you 59andthis formula :Age DateDiff = DATEDIFF(DATE(2022,4,30),DATE(1962,05,13),YEAR) gives 60Hope that will help you 😄- Bfaws
Helper III
Many thanks works perfectly for 59 years
- ScalaFrequent Visitor
Hello! Have you tried using the DAX-function DATEDIFF? I think this should be able to give you the value you want: DATEDIFF([Report Date],[Birthdate],YEAR)
Edit: actually it looks like datediff rounds up, so you would have to round down the result. You might be able to find additional ways of dealing with this in the following thread: Solved: Age and Age intervals, 3 questions - Microsoft Power BI Community