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

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
    )
)
  • yaman123's avatar
    yaman123
    Icon for Post Partisan rankPost Partisan
    5 years ago

    Thank you! This works a treat! 

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

    Hi Johanno, 

     

    Is there a way to use the dax measure you sent and only show employees who were active on the chosen month year also? would a seperate measure be required? 

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

      You could solve it in the same measure. If you want to remove inactive users from the whole table you could of course add a filter to the table. If you want them to be seen but without values for age you could change the IF statement to return BLANK() if status = inactive or so. Let me know if you need information!

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

        I would like to show only employees who were still in the business for that month. I have start and end dates in the employee table. How would i write this in the existing measure for age or will a new measure be required?