March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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)
)
Solved! Go to 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
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 )
)
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:
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 )
)
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)
)
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.
Thanks dear @MohammadLoran25 MohammadLoran25,
id modified it. could you please help?
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?
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?
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.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
27 | |
17 | |
16 | |
12 | |
11 |
User | Count |
---|---|
36 | |
27 | |
26 | |
20 | |
15 |