Forum Discussion
Date measures calculated on Date filter
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
Hi 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
Super UserHi 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