Calculating employee tenure 12 months before

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

Thanks in advance

[MohammadLoran25]

Hi @ejafarov ,

Based on the previous topic we had, If you mean the TenureMeasure for the Same Month of previous year, you can create this measure:

CALCULATE (
    [TenureInThePreviousTopic],
    FILTER (
        ALL ( DateTable ),
        DateTable[YearMonthIndex] = MAX ( DateTable[YearMonthIndex] )-12
    )
)