Date measures calculated on Date filter

[Jānis]

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
)

[MFelix]

Hi @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

[Jānis]

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 · Accepted 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

[Jānis]

Thx @MFelix

This is what I need and it works. You saved a lot of all.

Regards,

Jānis