Forum Discussion

yaman123's avatar
yaman123
Icon for Post Partisan rankPost Partisan
5 years ago
Solved

Calculate Employee Age Correctly

Hi all,    I cant 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 which is used to select a month year (Aug 2020) et...
Need Help
Show and Tell
Tips and Tricks
  • Johanno's avatar
    Johanno
    5 years ago

    I think your measure gives you calender years. Try this:

    Employee Age = 
VAR SelectedDate =MAX(Dates[Date])
VAR CurrentEmployee = MAX('Table'[BirthDate])
VAR YearDiff = YEAR(SelectedDate) - YEAR(CurrentEmployee)
RETURN
IF ( MONTH(SelectedDate) > MONTH(CurrentEmployee),
    YearDiff,
    IF (MONTH(SelectedDate) = MONTH(CurrentEmployee) && DAY(SelectedDate) >= DAY(CurrentEmployee),
        YearDiff,
        YearDiff-1
    )
)
  • Johanno's avatar
    Johanno
    5 years ago

    Yeah, that's true. Maybe this:

    Result = 
VAR EmployeeAge = SUM('Table'[Value])
VAR Result = IF(  OR(
                    TODAY() >= MAX('Table'[StartDate]) && TODAY() <= MAX('Table'[EndDate]),
                    TODAY() >= MAX('Table'[StartDate]) && ISBLANK(MAX('Table'[EndDate]))),  
                EmployeeAge,
                BLANK()
)
RETURN Result

     

Johanno's avatar
Johanno
Icon for Continued Contributor rankContinued Contributor
5 years ago

Can you share your measure where you do the subtraction?

 

I guess if you extract YEAR, MONTH and DAY from each date it should work.

yaman123's avatar
yaman123
Icon for Post Partisan rankPost Partisan
5 years ago

Hi, 

 

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