Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm trying to make new measure, which will calculate Average Age of Employees. The code below includes all employees in all periods, but i need to calculate average age only on active employees, for example in 2016. I have column "Emeployment Start Date" and "Employment End Date" in table Employee, which means that employee active period is between Emp.Start Date and Emp.End Date. I don't know how to use filter for calculated measure.
AverageAge = AVERAGEX( CALCULATETABLE( Employee; Employee[Employment Date]<>BLANK(); Employee[Birth Date]>DATE(1910;1;1) ); DATEDIFF( Employee[Birth Date]; LASTDATE('Date'[Date]); MONTH )/12 )
Solved! Go to Solution.
Hi @Jānis,
I have created 4 measures this is to make the calculations more self explanatory and more accurate due to the filters that you need to have:
Age_per_employee = VAR StartYear = MIN ( DimDate[Date] ) VAR EndYear = MAX ( DimDate[Date] ) VAR emp = MIN ( Employee[Birthdate] ) RETURN CALCULATE ( DATEDIFF ( emp; EndYear; YEAR ) ) Active_Employees = VAR StartYear = MIN ( DimDate[Date] ) VAR EndYear = MAX ( DimDate[Date] ) RETURN CALCULATE ( COUNT ( Employee[No_] ); FILTER ( ALL ( Employee[Employment Start Date]; Employee[Employment End Date] ); IF ( Employee[Employment End Date] = BLANK (); Employee[Employment Start Date] <= EndYear; Employee[Employment Start Date] <= EndYear && Employee[Employment End Date] >= StartYear ) ) ) Active_Emp_age = VAR StartYear = MIN ( DimDate[Date] ) VAR EndYear = MAX ( DimDate[Date] ) RETURN CALCULATE ( [Age_per_employee]; FILTER ( ALL ( Employee[Employment Start Date]; Employee[Employment End Date] ); IF ( Employee[Employment End Date] = BLANK (); Employee[Employment Start Date] <= EndYear; Employee[Employment Start Date] <= EndYear && Employee[Employment End Date] >= StartYear ) ) ) Average_Age = AVERAGEX(ALL(Employee[No_]);[Active_Emp_age])
As you can see below the average age is calculated for the active employees, and then I make the average of the active ones the result can be seen from 2012 to 2016 where the number of employees is the same but the average age increases 1 year.
See attach PBIX file.
Regard,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Jānis,
You need to have a filter added to your measure in order to selected the start and end date within that period, don't know if you have a date table for making your visuals but assuming you have it you should do something like this:
Active_Emp_Age = VAR StartYear = MIN ( DimDate[Date] ) VAR EndYear = MAX ( DimDate[Date] ) RETURN CALCULATE ( [AverageAge]; FILTER ( ALL ( Employee[Column] ); Employee[Employment Start Date] <= EndYear && Employee[Employment End Date] >= StartYear ) )
Believe this will work, but you can have to make some changes, if you need please share some sample data so I can set it up for you.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix,
Thanks for your answer. I tried some experiments from your answer, bet I didn't get the result what I need.
Sample of data:
I need picture (see below), but with calculation only with active employees. For example in 2010 I have 2 active employees, so the AverageAge calculation should be only from those 2 active employess, in 2011 from 4 active employees, in 2018 from 9 active employees (excluded employees with Inactive Date 2017) and so on. And I have a date table.
Regards,
Jānis
Hi @Jānis,
I have created 4 measures this is to make the calculations more self explanatory and more accurate due to the filters that you need to have:
Age_per_employee = VAR StartYear = MIN ( DimDate[Date] ) VAR EndYear = MAX ( DimDate[Date] ) VAR emp = MIN ( Employee[Birthdate] ) RETURN CALCULATE ( DATEDIFF ( emp; EndYear; YEAR ) ) Active_Employees = VAR StartYear = MIN ( DimDate[Date] ) VAR EndYear = MAX ( DimDate[Date] ) RETURN CALCULATE ( COUNT ( Employee[No_] ); FILTER ( ALL ( Employee[Employment Start Date]; Employee[Employment End Date] ); IF ( Employee[Employment End Date] = BLANK (); Employee[Employment Start Date] <= EndYear; Employee[Employment Start Date] <= EndYear && Employee[Employment End Date] >= StartYear ) ) ) Active_Emp_age = VAR StartYear = MIN ( DimDate[Date] ) VAR EndYear = MAX ( DimDate[Date] ) RETURN CALCULATE ( [Age_per_employee]; FILTER ( ALL ( Employee[Employment Start Date]; Employee[Employment End Date] ); IF ( Employee[Employment End Date] = BLANK (); Employee[Employment Start Date] <= EndYear; Employee[Employment Start Date] <= EndYear && Employee[Employment End Date] >= StartYear ) ) ) Average_Age = AVERAGEX(ALL(Employee[No_]);[Active_Emp_age])
As you can see below the average age is calculated for the active employees, and then I make the average of the active ones the result can be seen from 2012 to 2016 where the number of employees is the same but the average age increases 1 year.
See attach PBIX file.
Regard,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |