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. ...
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
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