Calculating employee tenure based on selected date

[ejafarov]

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)
)

[MohammadLoran25 · Accepted solution]

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

[DragosR]

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.

Thanks in advance!

[tamerj1 · Accepted solution]

Hi @ejafarov 
Please try

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 )
    )

[ejafarov]

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)
)

[MohammadLoran25]

@ejafarov 

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.

[ejafarov]

Measure12Months =

CALCULATE (

    [TenureNew],

    FILTER (

        ALL ( Teqvim[Date] ),

        Teqvim[Date] < MAX (Teqvim[YearMonthIndex] )

            && Teqvim[Date]

                >= MAX ( Teqvim[YearMonthIndex] ) - 12

    )

) it gives me a blank

[ejafarov]

Thanks dear @MohammadLoran25 MohammadLoran25, 
id modified it. could you please help?

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

[MohammadLoran25]

@ejafarov 

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?

[ejafarov]

dear @MohammadLoran25 , thanks for your great support.

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.

Could you please help?

[MohammadLoran25]

You're welcome @ejafarov ,

Follow these steps:

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.

[ejafarov]

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

[MohammadLoran25 · Accepted solution]

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