cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

## Calculating employee tenure based on selected date

Hi dears,

I want to calculate employe tenure for both active and resigned employees from selected date from filter.If an employee is active, the formula will calculate the tenure by finding the difference between the start date and the selected date from the filter.

If an employee is resigned after selected date, the formula will calculate the tenure by finding the difference between the start date and the selected date
If an employee is resigned before selected date, the formula will calculate the tenure by finding the difference between the start date and resigned date
if selected date is before start date the tenure will be blank

the dax formula I wrote is below:

but when I make filter date it doesn't show the resigned employee tenure. COuld you please help?

Measured Tenure =

VAR SelectedDate = MAX(Teqvim[Date])

RETURN
CALCULATE (
AVERAGEX(
FILTER (
'Final',
Final[Start Date] <= SelectedDate
&& (ISBLANK(Final[Resignation date]) || Final[Resignation date] > SelectedDate)
),
SWITCH(
TRUE(),
SelectedDate < Final[Start Date], BLANK(),
SelectedDate < Final[Resignation date] || ISBLANK(Final[Resignation date]),
DATEDIFF(Final[Start Date], SelectedDate, MONTH),
DATEDIFF(Final[Start Date], Final[Resignation date], MONTH)
)
),
CROSSFILTER(Teqvim[Date], Final[Start Date], None)
)

2 ACCEPTED SOLUTIONS
Super User

Hi @ejafarov ,

It is because of your FILTER function and the condition: Final[Resignation date] > SelectedDate

Try the measure below:

``````TenureNew =
CALCULATE(AVERAGEX (
'Final',
SWITCH (
TRUE (),
MAX ( DateTable[Date] ) < Final[Start Date], BLANK (),
MAX ( DateTable[Date] ) < Final[Resignation date]
|| ISBLANK ( Final[Resignation date] ), DATEDIFF ( Final[Start Date], MAX ( DateTable[Date] ), MONTH ),
DATEDIFF ( Final[Start Date], Final[Resignation date], MONTH )
)
),
CROSSFILTER(Teqvim[Date], Final[Start Date], None)
)``````

If this answer solves your problem, please give it a thumbs up and mark it as an accepted solution so the others would find what they need easier.

Regards,
Loran

Super User

Hi @ejafarov

``````Measured Tenure =
VAR SelectedDate =
MAX ( Teqvim[Date] )
VAR T1 =
CALCULATETABLE ( Final, CROSSFILTER ( Teqvim[Date], Final[Start Date], NONE ) )
VAR T2 =
FILTER ( T1, Final[Start Date] <= SelectedDate )
RETURN
AVERAGEX (
T2,
VAR StartDate = Final[Start Date]
VAR EndDate =
COALESCE ( Final[Resignation Date], TODAY () )
RETURN
DATEDIFF ( StartDate, MIN ( EndDate, SelectedDate ), MONTH )
)``````
11 REPLIES 11
New Member

Hello,

Can you please help with a similar situation as above, the only difference on my situation is that I need a measure to count the different tenures in time?

Also, need to show the past YOY, MOM and YTD, MTD values for the tenures.

Can I use instead of AverageX, the CountX?

Data set looks like this:

Employee ID, Hire Date (d mmmm yyyy), Termination Date (same as Hire Date), Tenure in months (date diff between hire date and today), Tenure Range calculated as below:

SWITCH(TRUE(), 'Employees HHP'[Tenure] <= 3, "0-3 months", 'Employees HHP'[Tenure] <= 6, "3-6 months", 'Employees HHP'[Tenure] <= 12, "6-12 months", 'Employees HHP'[Tenure] > 12, "12 plus")

Need to show the tenure in buckets as above (3-6, 12 plus etc.) but not "3 6 months" shown as a total in a period of time (in different days, different values, in different months, diff values etc.), shown dinamic (or incremental) and not static values.

Super User

Hi @ejafarov

``````Measured Tenure =
VAR SelectedDate =
MAX ( Teqvim[Date] )
VAR T1 =
CALCULATETABLE ( Final, CROSSFILTER ( Teqvim[Date], Final[Start Date], NONE ) )
VAR T2 =
FILTER ( T1, Final[Start Date] <= SelectedDate )
RETURN
AVERAGEX (
T2,
VAR StartDate = Final[Start Date]
VAR EndDate =
COALESCE ( Final[Resignation Date], TODAY () )
RETURN
DATEDIFF ( StartDate, MIN ( EndDate, SelectedDate ), MONTH )
)``````
Helper I

Dears,

How can I find employee tenure 12 months before from selected date?

The below formula does not work correctly.

Tenure for 12 months before =
VAR SelectedDate = MAX(Teqvim[Date])
VAR RelevantPeriodStart = EDATE(SelectedDate, -12)
VAR T1 = CALCULATETABLE(Final, CROSSFILTER(Teqvim[Date], Final[Start Date], NONE))
VAR T2 = FILTER(T1, Final[Start Date] <= SelectedDate && Final[Start Date] >= RelevantPeriodStart)
RETURN
AVERAGEX(
T2,
VAR StartDate = Final[Start Date]
VAR EndDate = COALESCE(Final[Resignation Date], TODAY())
RETURN DATEDIFF(StartDate, MIN(EndDate, SelectedDate), MONTH)
)

Super User

1-In your date table create a calculated column:

YearMonthIndex=YEAR(DateTable[Date])*12+MONTH(DateTable[Date])

2-A measure as below:

``````Measure12Months =
CALCULATE (
[TenureNew],
FILTER (
ALL ( DateTable ),
DateTable[YearMonthIndex] < MAX ( DateTable[YearMonthIndex] )
&& DateTable[YearMonthIndex]
>= MAX ( DateTable[YearMonthIndex] ) - 12
)
)``````

Which TenureNew is the measure of my previous response.

Helper I
Measure12Months =
CALCULATE (
[TenureNew],
FILTER (
ALL ( Teqvim[Date] ),
Teqvim[Date] < MAX (Teqvim[YearMonthIndex] )
&& Teqvim[Date]
>= MAX ( Teqvim[YearMonthIndex] ) - 12
)
) it gives me a blank
Helper I

Measure12MonthsTenure =
VAR CurrentYearMonthIndex = MAX(Teqvim[YearMonthIndex])
RETURN
CALCULATE (
[Measured Tenure],
FILTER (
ALL ( Teqvim ),
Teqvim[YearMonthIndex] < CurrentYearMonthIndex
&& Teqvim[YearMonthIndex] >= CurrentYearMonthIndex - 12
)
)

I think it gives me the wrong value. the logic is that

if the employee is resigned before the selected date of 12 months the tenure will be different between the start date and resigned date , else will be a different start selected date
if the employee start date is before selected date of 12 months before it will blank

Super User

Oh I think I misunderstood it.

It seems that whay you need is the Tenure measure for parallel period?

For example you want to compare the tenure of Jan2023 with Jan2022 (12months before Jan2023).

Right?

Helper I

I want to write a measure that calculates an employee's tenure 12 months before the date I select from the filter. For example, if I select 31.12.2022 from the date filter, I want to calculate the tenure for the employee as of 01.01.2022.

For instance, let's say Jon was hired on 01/01/2020 and is still working. If the date 31.12.2022 is filtered, I would calculate the tenure as of 01.01.2022 by finding the month difference between 01.01.2022 and 01.01.2020.

Here's the condition: if the employee resigned before the selected date of 12 months, the tenure will be different between the start date and the resignation date. Otherwise, it will be different from the start date selected if the employee's start date is before the selected date of 12 months before.

Super User

You're welcome @ejafarov ,

1-In your date table, create a calculated column:

``YearMonthIndex = (DateTable[Year])*12+DateTable[Month]``

2-In your final table create these 2 calculated columns:

``StartYearMonthIndex = YEAR(Final[Start Date])*12+MONTH(Final[Start Date])``
``ResignYearMonthIndex = YEAR(Final[Resignation Date])*12+MONTH(Final[Resignation Date])``

3-Then Create the measure:

``````Tenure12Months =
CALCULATE (
AVERAGEX (
'Final',
SWITCH (
TRUE (),
MAX ( DateTable[YearMonthIndex] ) < Final[StartYearMonthIndex] - 11, BLANK (),
Final[ResignYearMonthIndex]
< MAX ( DateTable[YearMonthIndex] ) - 11
&& NOT ISBLANK ( Final[Resignation date] ), DATEDIFF ( Final[Start Date], Final[Resignation date], MONTH ),
Final[StartYearMonthIndex]
< MAX ( DateTable[YearMonthIndex] ) - 11
|| (
ISBLANK ( Final[Resignation date] )
&& Final[StartYearMonthIndex]
< MAX ( DateTable[YearMonthIndex] ) - 11
),
DATEDIFF (
Final[Start Date],
CALCULATE (
MIN ( DateTable[Date] ),
FILTER (
ALL ( DateTable ),
DateTable[YearMonthIndex]
= MAX ( DateTable[YearMonthIndex] ) - 11
)
),
MONTH
)
)
),
CROSSFILTER ( DateTable[Date], Final[Start Date], NONE )
)``````

I Checked it and it worked for both your Senario that you just mentioned.

Helper I
TenureNew12MonthsBefore =
VAR SelectedDate = MAX(Teqvim[Date])
VAR Date12MonthsBefore = EOMONTH(SelectedDate - 365, 0)
RETURN
CALCULATE(
AVERAGEX(
'Final',
SWITCH(
TRUE(),
Final[Start Date] > Date12MonthsBefore, BLANK(),
SelectedDate < Final[Resignation date] || ISBLANK(Final[Resignation date]),
DATEDIFF(Final[Start Date], Date12MonthsBefore, MONTH),
DATEDIFF(Final[Start Date], Final[Resignation date], MONTH)
)
),
CROSSFILTER(Teqvim[Date], Final[Start Date], None),
FILTER(ALL(Teqvim), Teqvim[Date] = SelectedDate)
)

I tried this I think it gives me wrong number
Super User

Hi @ejafarov ,

It is because of your FILTER function and the condition: Final[Resignation date] > SelectedDate

Try the measure below:

``````TenureNew =
CALCULATE(AVERAGEX (
'Final',
SWITCH (
TRUE (),
MAX ( DateTable[Date] ) < Final[Start Date], BLANK (),
MAX ( DateTable[Date] ) < Final[Resignation date]
|| ISBLANK ( Final[Resignation date] ), DATEDIFF ( Final[Start Date], MAX ( DateTable[Date] ), MONTH ),
DATEDIFF ( Final[Start Date], Final[Resignation date], MONTH )
)
),
CROSSFILTER(Teqvim[Date], Final[Start Date], None)
)``````

If this answer solves your problem, please give it a thumbs up and mark it as an accepted solution so the others would find what they need easier.

Regards,
Loran

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors