Forum Discussion

Vantage111's avatar
Vantage111
Frequent Visitor
3 years ago
Solved

Find Average age on a given date

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...
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    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

BA_Pete's avatar
BA_Pete
Super User
3 years ago

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