Forum Discussion
Solved
Modifying DAX Formula for Age Calculation to Include Active Employees Hired Before the Selected Year
I used the DAX formula below to create a list of active employees with their corresponding ages based on the latest year available from my Year visual filter using Between style for my slicer.
Age Measure =
VAR _selyear =
MAX(_Calendar[Year])
VAR _selbirthdate =
SELECTEDVALUE(hr_coredata[DOB])
VAR _age =
DATEDIFF(_selbirthdate,DATE(_selyear, 12, 31 ), YEAR )
RETURN
_age
How can I achieve the same results if I used the dropdown style that only allows a single selection. Currently, if I choose a single year, I will only get the list of active employees hired from the year, ignoring any active employees that are hired from previous year.
I have a calendar table and an hr_coredata table have the following columns -- [Employee_Name], [DOB] for birthdate, and [DateofHire] for reference. Thank you!
How can I achieve the same results if I used the dropdown style that only allows a single selection. Currently, if I choose a single year, I will only get the list of active employees hired from the year, ignoring any active employees that are hired from previous year.
I have a calendar table and an hr_coredata table have the following columns -- [Employee_Name], [DOB] for birthdate, and [DateofHire] for reference. Thank you!
Looks like you have an active relationship between Calender and hr_coredata[DateofHire]. You want to include all employees hired before the end of the selection year. I suggest changing the measure like this:
Age Measure = VAR _selyear = MAX(_Calendar[Year]) VAR _age = CALCULATE( DATEDIFF(SELECTEDVALUE(hr_coredata[DOB]),DATE(_selyear, 12, 31 ), YEAR ), ALL(_Calendar),_Calendar[Year]<=_selyear) RETURN _age
1 Reply
Looks like you have an active relationship between Calender and hr_coredata[DateofHire]. You want to include all employees hired before the end of the selection year. I suggest changing the measure like this:
Age Measure = VAR _selyear = MAX(_Calendar[Year]) VAR _age = CALCULATE( DATEDIFF(SELECTEDVALUE(hr_coredata[DOB]),DATE(_selyear, 12, 31 ), YEAR ), ALL(_Calendar),_Calendar[Year]<=_selyear) RETURN _age