Hello,
I’m trying to get a result between a start date and a termination date (or current date if no termination date) that will give me a result like 5 year, 3 months and 22 days. This isn’t working.
Time in Service =
VAR Today = NOW()
VAR HireDate = MAX(‘Employment History’[Hire Date])
VAR TerminationDate = MAX(‘Employment History’[Termination Date])
VAR EffectiveDate = IF(TerminationDate > Today, TerminationDate, Today)
VAR Years = DATEDIFF(HireDate, EffectiveDate, YEAR)
VAR Months = DATEDIFF(HireDate, EffectiveDate, MONTH)
VAR Days = DATEDIFF(HireDate, EffectiveDate, DAY)
VAR RemainingYears = IF(Years > 0, Years, 0)
VAR RemainingMonths = IF(Months > 0, Months - (RemainingYears * 12), 0)
VAR RemainingDays = IF(Days > 0, Days, 0)
RETURN
IF(RemainingYears > 0,
FORMAT(RemainingYears, “0”) & " Years, " & FORMAT(RemainingMonths, “0”) & " Months, " & FORMAT(RemainingDays, “0”) & " Days",
IF(RemainingMonths > 0,
FORMAT(RemainingMonths, “0”) & " Months, " & FORMAT(RemainingDays, “0”) & " Days",
FORMAT(RemainingDays, “0”) & " Days"
)
)