Forum Discussion

Bfaws's avatar
Bfaws
Icon for Helper III rankHelper III
3 years ago
Solved

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

  • Anonymous's avatar
    Anonymous
    3 years ago

    use this dax formula : 

    Age DateDiff = INT ( YEARFRAC ( DATE(2022,4,30), DATE(1962,05,13), 1 ) ) it will gives you 59 
    and 
    this formula : 
    Age DateDiff = DATEDIFF(DATE(2022,4,30),DATE(1962,05,13),YEAR) gives 60 
     
    Hope that will help you 😄 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    use this dax formula : 

    Age DateDiff = INT ( YEARFRAC ( DATE(2022,4,30), DATE(1962,05,13), 1 ) ) it will gives you 59 
    and 
    this formula : 
    Age DateDiff = DATEDIFF(DATE(2022,4,30),DATE(1962,05,13),YEAR) gives 60 
     
    Hope that will help you 😄 
    • Bfaws's avatar
      Bfaws
      Icon for Helper III rankHelper III

      Many thanks works perfectly for 59 years

  • Scala's avatar
    Scala
    Frequent 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