Hi,
I have the following measure to calculate various length of service milestones in the next month...
LOS Flag (31 Days) =
IF
('*Employee'[Time With Company (Days)] >= 334 && '*Employee'[Time With Company (Days)] <365, "One Year Flag",
IF
('*Employee'[Time With Company (Days)] >= 1794 && '*Employee'[Time With Company (Days)] <1825, "Five Year Flag",
IF
('*Employee'[Time With Company (Days)] >= 3619 && '*Employee'[Time With Company (Days)] <3650, "Ten Year Flag",
IF
('*Employee'[Time With Company (Days)] >= 5444 && '*Employee'[Time With Company (Days)] <5475, "Fifteen Year Flag",
IF
('*Employee'[Time With Company (Days)] >= 7269 && '*Employee'[Time With Company (Days)] <7300, "Twenty Year Flag",
IF
('*Employee'[Time With Company (Days)] >= 9094 && '*Employee'[Time With Company (Days)] <9125, "Twenty Five Year Flag"))))))
What I'd like to do is be able to look ahead and calculate who will hit those length of service milestones in the next calendar year. Tried that measure with a relative date slicer for in the next calendar year but no luck. Haven't a clue where to start with this one! Any guidance appreciated. Yes, I have a date table, Time With Company [Days] is a calculated column based off contract start date.
