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
Super UserHi 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
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
- MFelix
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