Modifying DAX Formula for Age Calculation to Include Active Employees Hired Before the Selected Year

dataaanana — Mon, 20 Nov 2023 09:06:10 GMT

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!

Re: Modifying DAX Formula for Age Calculation to Include Active Employees Hired Before the Selected Year

sjoerdvn — Mon, 20 Nov 2023 10:03:42 GMT

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

topic Re: Modifying DAX Formula for Age Calculation to Include Active Employees Hired Before the Selected Year in DAX Commands and Tips

Modifying DAX Formula for Age Calculation to Include Active Employees Hired Before the Selected Year

Re: Modifying DAX Formula for Age Calculation to Include Active Employees Hired Before the Selected Year