Forum Discussion
Calculating Employee Age by Month
Hi
Please see link to dummy file below:
https://1drv.ms/u/s!AkENRXlfBIJKig49BFN8Y8DQwgJ5?e=v3DW0w
I am struggling to create a measure that calculates the age of an Employee at any given date, in this case, the start of each month.
I need this in order to calculate the average age of employees, by company, by division as time goes by.
You will notice that each employee has a unique id (PersRef) and that they may have more than one role (OccRef). Each role can be one of a number of Posn:Basis values and these can potentially change over time (e.g., an employee may change from full time to part time).
All the date fields have inactive relationships with the Dates table. The min date in the Dates table is equal to the earliest employee date of birth and the max date is equal to the first day of the current month.
When I try to input a measure for Age =
19 Replies
Hi again, if it helps I know from the data that 2169 roles were in existence as at 1st Sept 2021 and that the average of all employee ages associated with those roles equates to 47.38. This is the figure I would like to see as a total against Sept-21. Let me know if a link to the data in Excel would help.
Would really appreciate any help on this one - thanks 🙂
- TheoC
Community Champion
Hi Anonymous
Massive thanks for the additional information and the data link would be very beneficial!
I am on phone until the morning but will get you a solution first thing (8.20pm in Brisbane), unless one of the insanely talented / gifted members in the Community respond with it first.
Either way, you will have a solution within 12 hours 🙂
Thanks so much TheoC. Christmas might actually come early for once!! This has been an ongoing nightmare for a whole week now, so another 12 hours certainly won't hurt!!
Link to the data in Excel if that helps ...... https://1drv.ms/x/s!AkENRXlfBIJKig9xz9eUmLvxHFOS?e=1MNfBt
Anything to the right of column I on the Role_Ref worksheet is just my crude workings giving me an idea as to what I should be seeing against each company, for each month start date.
Please shout if you cannot access the link, or if there is anything you feel I haven't been entirely clear about.
- TheoC
Hi Anonymous
I recommend using Power Query to calculate. All you need to do is:
- Go to Add Column
- Click on the Date button
- Click on Age
- Select the new column, then click on Duration button and select the type of duration you want (i.e. Years, Months, Days, etc).
From here, you will have a column that you can manipulate as you require when using DAX.
Otherwise, if you're wanting to use DAX, then you can use the following:
Age (Rounded to Year ) = INT ( YEARFRAC ( EE_Ref[DoB] , EOMONTH ( TODAY () , -1 ) , 1 ) )Age (Not Rounded) = YEARFRAC ( EE_Ref[DoB] , EOMONTH ( TODAY () , -1 ) , 1 )Hope this helps 🙂
Hi TheoC ,
Really appreciate the reply, thanks.
Sorry if I am being thick or have got the wrong end of the stick but the Power Query solution only appears to provide age as of today, whereas I am needing each employee's age to be calculated as at any given date, in this case as at the start of each month. I then need this to be applied to the subset of employee data achieved using either the Current Head or Current Role measures.
Basically I need to be able to answer the question "ok so that's the average age now, but how does that compare to the workforce six months ago, or a year / two years ago??".
So....
gives me a snapshot of distinct roles as at the start of each month.......
but I need the values to reflect the average age of the employees in those roles, so the aim is for something like........
The filters in the Current Roles measure above compare dates against min(Dates[Date]) which looks at the minimum date for the month (each matrix column) in question. However, when I try to use similar logic for the Age calculation is uses the overall minimum date in the Dates table, which is set to be the earliest date of birth by default :(.
I feel sure that it has something to do with filter, crossfilter or even the inactive date relationships in place.
That said, I am also sure that I had hair at the start of this journey, but it's all been pulled out!
Any other ideas greatly appreciated - thanks
Hi Theo
Worth pointing out here that the Power Query method doesn't take account of leap years and is dividing the total number of days by 365 rather than 365.25. Some ages will therefore be incorrect.
Hi Theo
Still struggling on with this one, but now using the age measure below. On its own, it calculating correctly as at each month start, but its then a question as to how do I link to the cohorts identified by either Current Role and Current Head measures and then apply average??
- TheoC
Hi Anonymous
I believe it may have to do with the data itself. This is the only thing I can put it down to.
I am so sorry I couldn't be of more assistance!
TheoC Greg_Deckler , Hi guys, just wondering if you've been able to crack this? thanks.
