Calculating & forecasting length of Service

Tob_P — Fri, 10 Mar 2023 17:38:39 GMT

Hi,

I have the following measure to calculate various length of service milestones in the next month...

LOS Flag (31 Days) =

('*Employee'[Time With Company (Days)] >= 334 && '*Employee'[Time With Company (Days)] <365, "One Year Flag",

('*Employee'[Time With Company (Days)] >= 1794 && '*Employee'[Time With Company (Days)] <1825, "Five Year Flag",

('*Employee'[Time With Company (Days)] >= 3619 && '*Employee'[Time With Company (Days)] <3650, "Ten Year Flag",

('*Employee'[Time With Company (Days)] >= 5444 && '*Employee'[Time With Company (Days)] <5475, "Fifteen Year Flag",

('*Employee'[Time With Company (Days)] >= 7269 && '*Employee'[Time With Company (Days)] <7300, "Twenty Year Flag",

('*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.

Re: Calculating & forecasting length of Service

Tob_P — Fri, 10 Mar 2023 18:37:41 GMT

Here's some sample data with expected output

https://docs.google.com/spreadsheets/d/1e8plunXupHitLDIDOdbm0IN7rVrFBMj5/edit?usp=sharing&ouid=108389835693298469342&rtpof=true&sd=true

Re: Calculating & forecasting length of Service

tamerj1 — Sat, 11 Mar 2023 14:53:19 GMT

Hi Tob_P

what results are you getting with this "measure"?

does the the relative date slicer table have a relationship with the main table?

topic Re: Calculating & forecasting length of Service in DAX Commands and Tips

Calculating & forecasting length of Service

Re: Calculating & forecasting length of Service

Re: Calculating & forecasting length of Service