Forum Discussion
Calculating Employee Age by Month
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?
Community ChampionHi Anonymous
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
Hi Theo
The measure errors ......
What am I missing?! 😩 It's too early for the pub here, so that's not an option!!
- TheoC
Anonymous also, if that doesn't work, try this:
Age (Not Rounded) =
VAR _MaxxRet = MAX(EE_Ref[DoB] )
RETURN
YEARFRAC ( EE_Ref[DoB] , _MaxxRet , -1 ) - TheoC
Anonymous 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
Anonymous 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)
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?