Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Calculating Employee Age by Month

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...
Need Help
TheoC's avatar
TheoC
Icon for Community Champion rankCommunity Champion
4 years ago

Hi Anonymous 

 

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 🙂

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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