Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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!!
Solved! Go to 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
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
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.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |