Forum Discussion
Calculating Employee Age by Month
Community ChampionHi 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
You're a legend Anonymous. Thank you! Will touch base in the morning and look forward to speaking soon!
Theo 😀
- TheoC
Anonymous 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?