How to calculate somebodys age over time

[AC_71]

Hi,

I am relatively new to the joys of Power BI, so please be gentle!

I am hoping somebody here can help with a problem I have been having trying to produce a chart of the average age of a group of employees over time, taking into account the starting and leaving dates of each employee.

I have been able to produce this chart showing the average, but the issue I am having is that the each employees age does not change over time. They should increase year on year.

I have a dates table and an employees table (contains Name, DOB, Start Date, Leaving Date, etc.).

How do I create a measure in DAX that can calculate this dynamic age?

Many Thanks!

[Kerry_M]

Try this (charting on a chart where the X axis uses a field from the MyCalendar table):

AgeOverTime =

VAR AverageBirthYear =   CALCULATE(YEAR(AVERAGE('Table'[Birth Date])),
    'Table'[employmentstart]<Max(MyCalendar[Date]) && 'Table'[employmentend]>Max(MyCalendar[Date]))

RETURN

If(AverageBirthYear=blank(),blank(),  Max(MyCalendar[Calendar Year])-AverageBirthYear)

[AC_71]

Does this sufficiently explain my problem?

I am sure this is quite straightforward for the experts but is beyond my current capabilities. Any help would be appreciated as I need this quite urgently.

Many Thanks in advance!

[Anonymous]

Hi @AC_71 ,

In order to better understand your demands and give the right solution, could you please provide some more specific information? such as your desensitized example data and a screenshot of your desired results?

Please try a measure like below:

Measure =
YEARFRAC ( SELECTEDVALUE ( 'employees'[DOB] ), TODAY (), 1 )

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_Binbin Yu

[AC_71]

Hi, I am not sure how else to explain this.

I have a date table and an employee data table. In the employee table it contains a date of birth, from which I can calculate the employees age.

Using a single person as an example, if create a chart of the employees age (they are 52 this year) it would look like this:

However, this is not correct, as next year they would be 53, the following year 54, etc. Similarly if we went back 10 years, they would be 42. The chart should look like this:

This is the bit I am struggling with.

Ultimately I will apply this to all the employees so I can chart an average age of the total staff over time, takling into account start/leaving dates. This bit I can do!

[AC_71]

When I try this, I get a message saying : 'A single value for column 'DOB' in table 'EmployeeInfo' cannot be deterrmined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.'

[AC_71]

Also, this only calculates the age as of now. I want to be able to see the age in 10 years or so. If I plot it on a graph, it should be sloping upwards over time. Thanks

[Idrissshatila]

Hello,

use this measure as a calculated column

age = Year(today())- Year(employees[DOB])

If I answered your question, please mark my post as solution, Appreciate your Kudos👍

Follow me on Linkedin