Average age for active employees over time with a slicer

[baggyb]

Hi

I've linked to the pbix and Excel source file below (all names and details are fake)

Testing Dashboard 

I'm creating a dashboard for HR and I've run into an issue. One of the pages is going to be a demographic breakdown of the company. I've included a sample of this in the link - the real version is much more detailed (and attractive!).

I can set everything up so it provides a snapshot of the company now, but I also want the ability to look back at any point in time. I have created a measure which shows the correct number of active employees at any date and it works perfectly. I can then use this for some other measures and graphs. This uses a couple of other measures to look at hires and leavers to work out how many employees were actually employed on any given date.

Cumulative Head Count =

CALCULATE( [Hire Count] - [Leaver Count],

FILTER( ALLSELECTED( Dates ),

Dates[Date] <= MAX( Dates[Date] ) )

)

However, where I'm having the issue is for the average age and average tenure. Currently, these are simply calculated as an average of the number of years in the age and tenure columns in the data table. I've been trying but I can't seem to make a measure which will also show these correctly through time. For example, if I set the date in the slicer to 01/01/2020 it shows me that there were 69 active employees and other stats which only link to those employees, but I can't figure out how to do it for age and tenure. I'm probably missing something very simple but any help would be greatly appreciated!

Many thanks

[Kerry_M]

I just noticed that you calculate age using TODAY() but that would not be correct for previous years so I modified the formula to use _SelectedDate to calculate age.

[Kerry_M]

Thanks! I have been looking for almost the exact same formula construction.

[v-rzhou-msft · Accepted solution]

Hi @baggyb ,

I suggest you to try codes as below to calculate average age and average tenure for active employees by measure.

Avg Age = 
VAR _SELECTDATE =
    SELECTEDVALUE ( Dates[Date] )
VAR _ADD =
    ADDCOLUMNS ( ALL ( 'Table' ), "Age", DATEDIFF ( [DOB], TODAY (), YEAR ) )
VAR _FILTER =
    FILTER (
        _ADD,
        [Hire Date] <= _SELECTDATE
            && OR ( [Leave Date] = BLANK (), [Leave Date] > _SELECTDATE )
    )
RETURN
    AVERAGEX ( _FILTER, [Age] )

Avg Tenure = 
VAR _SELECTDATE =
    SELECTEDVALUE ( Dates[Date] )
VAR _ADD =
    ADDCOLUMNS ( ALL ( 'Table' ), "Tenure", DATEDIFF ( [Hire Date], [Leave Date], YEAR ) )
VAR _FILTER =
    FILTER (
        _ADD,
        [Hire Date] <= _SELECTDATE
            && OR ( [Leave Date] = BLANK (), [Leave Date] > _SELECTDATE )
    )
RETURN
    AVERAGEX ( _FILTER, [Tenure] )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.