Calculating Employee Age by Month

[grapple_cumb]

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 =

int(datediff(EE_Ref[DoB],min(Dates[Date]),DAY)/365.25)

and then use this with an average alongside the current heads or current roles measure, it uses the earliest date of birth as opposed to the start of each calendar month.

Any help would be greatly appreciated.  It is starting to drive me crazy!

[grapple_cumb]

@TheoC @Greg_Deckler , Hi guys, just wondering if you've been able to crack this?  thanks.

[grapple_cumb]

@TheoC 

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 @grapple_cumb 

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]

Hi @grapple_cumb 

I recommend using Power Query to calculate.  All you need to do is:

1.  Go to Add Column
2. Click on the Date button
3. Click on Age
4. 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 🙂

[grapple_cumb]

@theo 

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.

[grapple_cumb]

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

[grapple_cumb]

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]

Hi @grapple_cumb 

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 🙂

[grapple_cumb]

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]

You're a legend @grapple_cumb. Thank you! Will touch base in the morning and look forward to speaking soon!

Theo 😀

[TheoC]

@grapple_cumb here is the PBIX file mate.  All sorted.  Just note that there will be a slight different in aging because I have used Power Query to give you the exact age from 1 October 2021 versus the calculation you're using being 365.25 which will lead to a small variance.

https://axiarcomau-my.sharepoint.com/:u:/g/personal/info_axiar_com_au/EX1pBDhMcW5GryE1FqkxkPABFgQPLD...

I've also added a couple of little things including the Age at Current Month Start and the Age at Last Month Start for you using PowerQuery. So each refresh, these will update.

Hope this helps mate!

Theo 🙂

[grapple_cumb]

@TheoC 

Hi Theo

Firstly, thank you for the time you've spent on this. Unfortunately, I'm afraid it's still not giving me what I'm after.  I need a measure that calculates the age of  cohort employees at any given month start.

For example, the Current Role measure identifies a cohort of 1832 for May-21 against Company O.

I need a measure that then calculates the ages of those employees that make up the 1832 at the start of May-21 (and a second measure that then averages the first measure, but this should be the easy part and I should be able to sort that).

The Power Query method is relatively static in nature.  It would give me average age as at most recent month start and one month prior to that but cannot dynamically calculate age according to any other month start.

If I had a Card showing an average age measure I would want it to change to 41.9 if I clicked on the 46 showing against Company G and Jun-21, or 47.8 if I clicked on the 1730 showing against Company O and Aug-21, and the matrix would look like this below if I used the average age measure as Value.

I think the most frustrating thing is that I have a got measure that tells me which employees fall into the bucket so to speak at any given month start but I cannot then work out what their ages were at that time.  So not only can I not then work out the average age at that point but I am also not able to apply those employees to age band buckets and see for example how the percentage of 31-40 year olds changes over time.

In my mind I am thinking that this surely must be achievable but then again I am a relative novice when it comes to Power BI so not fully aware of its limitations.  What do you think?

[TheoC]

Hi @grapple_cumb 

Thanks so much for the detailed explanation. I had no idea that you wanted it just to calculate age at a point in time (whatever that point in time be).

To achieve that, you should be able to use the following and adjust to the Date[Date] table and column given that is what you will be using for filtering and slicing:

Age (Rounded to Year ) = INT ( YEARFRAC ( EE_Ref[DoB] , Date[Date] , -1 ) , 1 ) )

Age (Not Rounded) = YEARFRAC ( EE_Ref[DoB] , Date[Date] , -1 ) 

Apologies - I am on phone and it is difficult to see everything in the text box and type properly. 

Let me know how it goes!

Theo

[grapple_cumb]

@TheoC 

Hi Theo

The measure errors ......

What am I missing?!  😩  It's too early for the pub here, so that's not an option!!

[TheoC]

@grapple_cumb I just read the pub comment 😂😂😂 absolute gem! We'll get this. It is much simpler than I initially thought.  I just don't have Power BI to test the syntax lol!

[TheoC]

@grapple_cumb let me sort it for you tomorrow because my phone is not getting it done. It's 8pm here in Brisbane.  But one last shot:

Age on Date =

VAR SelectedDate = SELECTEDVALUE(Date[Date])

VAR CurrentEmployee = MAX(EE_Ref[DoB])

RETURN

DATEDIFF(CurrentEmployee, SelectedDate, YEAR)

[TheoC]

@grapple_cumb also, if that doesn't work, try this:

Age (Not Rounded) = 

VAR _MaxxRet = MAX(EE_Ref[DoB] )

RETURN 

YEARFRAC ( EE_Ref[DoB] , _MaxxRet , -1 ) 

[grapple_cumb]

@TheoC 

Hi Theo

I took the last post of yours and tweaked it slightly. I'm sure that you didn't mean to compare a date of birth with a date of birth, and have put that down to you having had a long day at work!!

Subbed the Current Role measure in the first matrix with the new Age measure, giving the second matrix.  It appears to be looking at the start date of each month now, but having to aggregate the date of birth, in this case with the MAX function means that it is taking the most recent date of birth in the EE_ref table (17/7/2005) and comparing that with each month start date.

The Age measure (averaged) somehow needs to take account of the Current Role measure so the second matrix then shows average age of the cohort numbers in the first matrix (as per my dodgy excel snip in an earlier post).

[TheoC]

@Greg_Deckler mate, by chance, are you able to assist in breaking the age down at the row level and column level?