# Find yea, month, days... UNABLE TO REPLY

robertdseals

posted the following

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.