Forum Discussion
Calculating Employee Age by Month
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?