but i cannot reply directly because of a forum issue. I’ll continue the conversation here:
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"
)
)
@robertdseals, your measure has a few quirks. First, using NOW() for today’s date adds unnecessary complexity. Stick with TODAY() unless you really need datetimes. Also, the logic for figuring out the EffectiveDate seems a bit off. You’re checking whether TerminationDate is greater than Today. You probably want instead to check if TerminationDate is empty or if it’s actually before Today.
As for calculating years, months, and days individually—the idea isn’t wrong, especially if the aim is to break down time like “2 years, 1 month, 3 days.” But the way those pieces are stitched back together in RemainingYears, RemainingMonths, and RemainingDays will probably lead to some wonky results.
To make it easier for forum members to diagnose, you might consider posting a minimal working example in the form of a .pbix file.
Here’s what it should contain:
A single table named ‘Employment History’ with at least the following columns:
* Employee ID: Unique identifier for employees.
* Hire Date: The date when the employee was hired.
* Termination Date: The date when the employee was terminated. This could be blank for current employees.
Sample Data for ‘Employment History’:
| Employee ID | Hire Date | Termination Date |
|-------------|------------|------------------|
| 1 | 2015-01-01 | 2020-01-01 |
| 2 | 2018-06-15 | |
| 3 | 2017-12-12 | 2021-08-10 |
Also include your original ‘Time in Service’ DAX measure so that we can directly work on improving it along with a table visual displaying ‘Employee ID’ and your ‘Time in Service’ measure.