cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
guilherme_silva Frequent Visitor
Frequent Visitor

Average Age of employees by year dynamically

Hey, Guys, I have been facing some problems in calculating the average age of employees by year in my Power BI and I would like to ask some help.

 

I don't know how to dynamically calculate all the ages that the employees had in the year in order to get the average in that year and to check the evolution. I don't know how to add a virtual column based on the row context for the birthday, and the graph year at the same time. Above you can see that it isn't correct because in 2000 the people that are still enrolled had 19 years less than today, and I can't solve.

 

age.PNG

 

Does anyone have some idea on how to solve this?

 

The Data Set can be download here:

Average Age by Year (Data Set)

 

Thank you so much for your help!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
OwenAuger Super Contributor
Super Contributor

Re: Average Age of employees by year dynamically

@guilherme_silva 

 

Reuploaded your pbix here.

I had a look at your PBIX and I rewrote the Age (Dynamic) measure like this and put it in the Tornado Chart:

 

Age (Dynamic) = 
// Calculated average age of employees who are still 'active' as at the max selected date
// (if filtered by Year, this will be as at 31 December)
// Age is calculated as at the max selected date
VAR MaxDate =
    MAX ( 'Calendar'[Date] )
VAR MaxYear =
    YEAR ( MaxDate )
RETURN
    CALCULATE (
        AVERAGEX ( Employees, MaxYear - YEAR ( Employees[Birthday] ) ),
        ALL ( 'Calendar' ),
        Employees[AdmissionDate] <= MaxDate,
        OR (
            Employees[Resignation Date] > MaxDate,
            ISBLANK ( Employees[Resignation Date] )
        )
    )

After doing some checks it looks to be giving the correct result.

 

The logic is:

  1. Grab the MaxDate and MaxYear from the Calendar filter (this will be a year-end date on the Tornado)
  2. Iterate over the Employee table with AVERAGEX to get the average age, subject to these filters (applied within CALCULATE):
    1. Remove 'Calendar' filters otherwise we will be using the 'Calendar' table to filter Admission Date
    2. Admission Date must be on or before MaxDate
    3. Resignation Date must be after MaxDate or blank

Does this look like it's working?

 

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
Highlighted
OwenAuger Super Contributor
Super Contributor

Re: Average Age of employees by year dynamically

@guilherme_silva 

 

Reuploaded your pbix here.

I had a look at your PBIX and I rewrote the Age (Dynamic) measure like this and put it in the Tornado Chart:

 

Age (Dynamic) = 
// Calculated average age of employees who are still 'active' as at the max selected date
// (if filtered by Year, this will be as at 31 December)
// Age is calculated as at the max selected date
VAR MaxDate =
    MAX ( 'Calendar'[Date] )
VAR MaxYear =
    YEAR ( MaxDate )
RETURN
    CALCULATE (
        AVERAGEX ( Employees, MaxYear - YEAR ( Employees[Birthday] ) ),
        ALL ( 'Calendar' ),
        Employees[AdmissionDate] <= MaxDate,
        OR (
            Employees[Resignation Date] > MaxDate,
            ISBLANK ( Employees[Resignation Date] )
        )
    )

After doing some checks it looks to be giving the correct result.

 

The logic is:

  1. Grab the MaxDate and MaxYear from the Calendar filter (this will be a year-end date on the Tornado)
  2. Iterate over the Employee table with AVERAGEX to get the average age, subject to these filters (applied within CALCULATE):
    1. Remove 'Calendar' filters otherwise we will be using the 'Calendar' table to filter Admission Date
    2. Admission Date must be on or before MaxDate
    3. Resignation Date must be after MaxDate or blank

Does this look like it's working?

 

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

guilherme_silva Frequent Visitor
Frequent Visitor

Re: Average Age of employees by year dynamically

Hey, @OwenAuger!

 

I did the Age (Dynamic) that you rewrote and it's working exactly as I wanted to! Thank you so much for your time and your help.

 

I have just one question: Why did you use All( 'Calendar')? Was it necessary?

 

Thank you again!

OwenAuger Super Contributor
Super Contributor

Re: Average Age of employees by year dynamically

That's good news 🙂

 

Yes, the ALL ( 'Calendar' ) is necessary. The reason is that there is a relationship between Admission Date and the 'Calendar' table. If we didn't clear 'Calendar' filters for the purpose of the calculation, you would see average age only for employees who had Admission Dates within the year.

 

Try removing ALL ( 'Calendar' ) and you will see the difference.

 

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Artm Frequent Visitor
Frequent Visitor

Re: Average Age of employees by year dynamically

Hello all,

Is it possible to make the same but showing the average age not only by years but by months as well?

I have a visual calendar with years and months and want to see dynamic change of avg.age by months.

 

Thanks. 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)