Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Calculating Ave Age of Active Employees

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 NumberEmployee NameBirthdateEmployment Start DateLast Date Worked
0001Smith, John09/08/198402/01/200830/11/2017
0002Smith, Jane26/09/198315/08/201625/05/2017
0003Doe, John08/03/198127/02/201701/03/2017
0004Doe, Jane30/05/198522/03/201810/05/2018
0005Jackson, Mark09/03/198005/06/201809/10/2018
0006Jackson, Mich24/12/198609/07/201820/09/2018
0012Johnson, Kelly11/12/198623/07/201829/11/2018
0013Johnson, Jessica26/03/198414/11/201631/08/2018
0014James, LeBron25/07/198410/05/201504/03/2017
0015Messi, Lionel05/04/198414/03/201129/03/2018
0016Ronaldo, Cristiano28/10/198229/11/2018 
0017Agassi, Andre25/09/198305/12/2018 
0018Federer, Roger04/04/198619/09/2016 
0019Jordan, Michael22/06/198407/12/2018 
0020Nadal, Rafel20/12/198107/01/2019 
0021Woods, Tiger21/01/198409/01/2019 

 

Thank you!!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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
Not applicable

Hey,

 

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

 

Have a good day.

 

Louis

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() ) )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.