cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mrainey Member
Member

Displaying Tenure

What is a way to display the tenure of a person in the format of "X years, X months"? I was thinking of doing a simple DATEDIFF calculation between their hire date and today's date but from there I'm not sure how to get the DAX to simultaneously provide the number of years and months. Either year or months has to be selected when constructing that type of formula. Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisMendoza Established Member
Established Member

Re: Displaying Tenure

hello @mrainey,

 

Pretty much the same as @bccolema...

 

Tenure Calculated Column = 

VAR EndingDate =
IF(
Table3[termDate] = BLANK(),
TODAY(),
Table3[termDate]
)

VAR Yr = DATEDIFF(Table3[hireDate], EndingDate, YEAR)
VAR Mo = MOD(DATEDIFF(Table3[hireDate], EndingDate, MONTH),12)

VAR Tenure =
IF(
Yr > 0,
COMBINEVALUES(" ", Yr,"Years", Mo,"Months"),
COMBINEVALUES(" ", Mo,"Months")
)

RETURN Tenure
3 REPLIES 3
bccolema Regular Visitor
Regular Visitor

Re: Displaying Tenure

You could so something like this.  Calculate each piece separately.   

 

For example,  

 

Tenure Months
var years = text.from(datediff([start_Date],[enddate], year)) var months = text.from(datediff([start_Date],[enddate], month) - (datediff([start_Date],[enddate], year) *12)) years + " Years, " + months + " Months"
ChrisMendoza Established Member
Established Member

Re: Displaying Tenure

hello @mrainey,

 

Pretty much the same as @bccolema...

 

Tenure Calculated Column = 

VAR EndingDate =
IF(
Table3[termDate] = BLANK(),
TODAY(),
Table3[termDate]
)

VAR Yr = DATEDIFF(Table3[hireDate], EndingDate, YEAR)
VAR Mo = MOD(DATEDIFF(Table3[hireDate], EndingDate, MONTH),12)

VAR Tenure =
IF(
Yr > 0,
COMBINEVALUES(" ", Yr,"Years", Mo,"Months"),
COMBINEVALUES(" ", Mo,"Months")
)

RETURN Tenure
mrainey Member
Member

Re: Displaying Tenure

Thank you @ChrisMendoza and @bccolema ! Both work. Love having multiple ways to do things Smiley Happy