Calculating Ave Age of Active Employees

[PatrickLamoste]

Hello,

I am trying to get the Average Age (today) of only the active employees, basically employees that has the "Last Date Worked" blank.

Would it be possible to do an Ave using a filter? so far I tried the formula below but resulted in an error.

Average Ave Active EEs = DIVIDE (CALCULATE ( SUM ( AllStaff[Age] ) );CALCULATE (COUNTA ( AllStaff[Personnel number]);AllStaff[Employment End Date] <> BLANK()),0)

To calculate the age of all employees I used:  Age = INT (YEARFRAC(AllStaff[Date of Birth],TODAY()))

Personnel Number	Employee Name	Birthdate	Employment Start Date	Last Date Worked
0001	Smith, John	09/08/1984	02/01/2008	30/11/2017
0002	Smith, Jane	26/09/1983	15/08/2016	25/05/2017
0003	Doe, John	08/03/1981	27/02/2017	01/03/2017
0004	Doe, Jane	30/05/1985	22/03/2018	10/05/2018
0005	Jackson, Mark	09/03/1980	05/06/2018	09/10/2018
0006	Jackson, Mich	24/12/1986	09/07/2018	20/09/2018
0012	Johnson, Kelly	11/12/1986	23/07/2018	29/11/2018
0013	Johnson, Jessica	26/03/1984	14/11/2016	31/08/2018
0014	James, LeBron	25/07/1984	10/05/2015	04/03/2017
0015	Messi, Lionel	05/04/1984	14/03/2011	29/03/2018
0016	Ronaldo, Cristiano	28/10/1982	29/11/2018
0017	Agassi, Andre	25/09/1983	05/12/2018
0018	Federer, Roger	04/04/1986	19/09/2016
0019	Jordan, Michael	22/06/1984	07/12/2018
0020	Nadal, Rafel	20/12/1981	07/01/2019
0021	Woods, Tiger	21/01/1984	09/01/2019

Thank you!!

[Anonymous · Accepted solution]

Hi Patrick,

First of all, excuse me for my english which isn't that good 🙂

I didn't try my code but I think I'm not far of the answer...

Average Age = 
CALCULATE (
    AVERAGE(AllStaff[Age]);
    FILTER (
        AllStaff;
        NOT(AllStaff[Last Date Worked] = BLANK())
    )
)

Try it and tell me ! 🙂

Louis

[Anonymous]

Hey,

I finally tryed the code and it works well for me! 🙂

Have a good day.

Louis

[PatrickLamoste]

Thank you for that,I had to tweak it a bit to make it work. This formula works well for me.

Average Age = CALCULATE ( AVERAGE(AllStaff[Age] );FILTER ( AllStaff;AllStaff[Employment End Date] = BLANK() ) )