Tenure Days

[benz-fsproduce]

Hi, I am trying to calculate tenure days, in a new column, of employees based on my columns "last hire date" and "termination date". My current formula is: Tenure days = DATEDIFF(page[Last Hire Date], page[Termination Date], Day)

This formula only gives me the total tenure days of employees that have both a hire date and a termination date. So it is only counting the days of terminated employees. I want a formula that counts the tenure days of active employees as well. Thanks!

[HotChilli]

You could create a variable (something like var1 = COALESCE ((terminationD, TODAY()) )

and then use the variable in the datediff.

I think COALESCE works in this context, if not you can explicitly check for a null or blank termination date .

[amitchandak · Accepted solution]

@benz-fsproduce , assume Termination Date is blank for active employess

Tenure days = DATEDIFF(page[Last Hire Date], coalesce(page[Termination Date], Today()) , Day)