Find Average age on a given date

[Vantage111]

I am putting together a simple HR dashboard. One of the measures is Average age when an employee joins:

Average Age = 
Calculate(AVERAGE(AllEmployees[age]),
USERELATIONSHIP(Allemployees[join_date],CalendarTAB[Date]))

This also allows me to visualize the staff ages by year:

HOWEVER: this is the average age on Join_Date, what I would really like to see is AVERAGE ACTUAL AGE for the year - ie. if I had the same 10 staff working from 2015 to date and they were all 30 when they joined, then the average age will not change and by 2023 it will still show as 30 when in fact the average age should be 38.

I have a dob column, age is calculated using datediff and the Join_Date, I can also get Current_Age using DateDiff and Now(), and I use a date table called CalendarTab.

Hope this makes sense.

[BA_Pete · Accepted solution]

Hi @Vantage111 ,

You want to use a measure something like this:

_avgAgeOverTime =
VAR __maxDate = MAX(CalendarTab[Date])
RETURN
AVERAGEX(
    FILTER(
        employeeTable,
        employeeTable[startDate] <= __maxDate
        && employeeTable[endDate] >= __maxDate
    ),
    DATEDIFF(employeeTable[dob], __maxDate, DAY) / 365.25
)

You just need to make sure to use a CalendarTab field (Date, Month, Year etc.) as your visual axis to ensure __maxDate gets populated.

Pete