Calculate Employee Age Correctly

user_12859783 0
Viewed 153

I can't seem to find the answer to this anywhere! I am trying to work out the age of employees in my report.

So I have a date table that is used to select a month year (Aug 2020) etc which returns the data for that specific month. I have an employee table with emp name, start date, end date, date of birth, etc.

I am trying to calculate the age of employees from the date of birth until the selected month year. But it is always giving me the incorrect value.

I want to show when I select July 2020 and an employee born on 15/08/1991, this should show as 28. But when I select Aug 2020, this should show as 29. But I am only getting back 29 for whatever month year I select.

Any help would be appreciated!

Yasir

Employee Age =
VAR SelectedDate =
    MAX ( 'Date'[Date] )
VAR CurrentEmployee =
    MAX ( Query1[DATE_OF_BIRTH] )
RETURN
    DATEDIFF ( CurrentEmployee, SelectedDate, YEAR )
dax powerbi
1 Answers

To compute the age of the employee at the selected date a simple way is to compute the difference between the two dates in days and then divide the result by 365.25

Employee Age =
VAR SelectedDate =
    MAX( 'Date'[Date] )
VAR CurrentEmployee =
    MAX( Query1[DATE_OF_BIRTH] )
RETURN
     INT( ( SelectedDate - CurrentEmployee ) / 365.25 )

A more precise formula can be found in this article from SQLBI.COM

Correct calculate of age in DAX from birthday

using DATEDIFF(CurrentEmployee, SelectedDate, YEAR) just computes the difference between the years of the two dates, which doesn't work to compute the age.

user_2489490
Related