Forum Discussion
Calculating Employee Age by Month
Community ChampionAnonymous 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.
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 🙂
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 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 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!