Forum Discussion

ejafarov's avatar
ejafarov
Icon for Helper I rankHelper I
3 years ago
Solved

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

  • MohammadLoran25's avatar
    MohammadLoran25
    3 years ago

    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

  • tamerj1's avatar
    tamerj1
    3 years ago

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

11 Replies

  • MohammadLoran25's avatar
    MohammadLoran25
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      ejafarov
      Icon for Helper I rankHelper I
      3 years ago

      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's avatar
        MohammadLoran25
        Icon for Solution Sage rankSolution Sage
        3 years ago

        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.

  • DragosR's avatar
    DragosR
    New Member
    3 years ago

    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!