How to Calculate the Average Seniority for active employees

[Mannai]

so i have a dataset of employees with start and end date and each employee have a seniority value as a number 

any ideas how to calculate the average seniority for only the active employees

Thanks

[v-yangliu-msft · Accepted solution]

Hi  @Mannai,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Date = CALENDAR(DATE(2000,1,1),DATE(2022,12,31))

2. Create measure.

datediff =
var _select=SELECTEDVALUE('Date'[Year])
return
IF(
    MAX('FACT TABLE'[work])="no",
    YEAR(MAX('FACT TABLE'[End date]))-YEAR(MAX('FACT TABLE'[entry date])) ,
_select - YEAR(MAX('FACT TABLE'[entry date]) ))

average seniority for only the active employees =
AVERAGEX(FILTER(ALLSELECTED('FACT TABLE'),'FACT TABLE'[work]="yes"),[datediff])

3. Result:

Use [Year] of the Date table as the slicer.

When [work]=”no”, it means that it has left the company and does not need to change with the slicer

When [work]=”yes”, it means that there is no resignation, and it changes with the slicer

Best Regards,

Liu Yang

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

[Mannai]

Thank you so much 😁

[lkalawski]

Hi @Mannai ,

By having these values:

you can check active employees based on the end date (or based on the flag) and use this measure to calculate average seniority:

Avg Seniority = CALCULATE(AVERAGE(Employee[Seniority]),Employee[End Date] = BLANK())

[Mannai]

thank you for the answer but what if i want to see the average in the past where the employee still active ?

[lkalawski]

@Mannai ,

In this case, you need to use a calendar that will help you calculate the Average for the selected month (period). In that case, a better option is to have a measure instead of calculated columns.

Please send me a piece of data so I can write a measure.

[Mannai]

this column contain the seniority of each employee until the date of the extract of data
is it possible to have this column dynamically for example :
if john have NOW15 years experience , if i change the date to 2021 i get only 14 years 
i hope you undrestand what i meant

Thank you for your Help