Employee Milestones

[diskovered]

I'm looking for format Employee Milestones this way. Script below gives me year of service including anniversary date. How do I make it so if employee anniversary (& keep it in this bucket for 30 days after anniversary date) fall in this bucket, names are added automatically? @Greg_Deckler 

SELECT *,
       CASE
           WHEN DATEADD(YEAR, DATEDIFF(Year, service_date, GETDATE()), service_date) < GETDATE()
           THEN DATEDIFF(Year, service_date, GETDATE()) + 1
           ELSE DATEDIFF(Year, service_date, GETDATE())
       END AS [ServiceAwardDateNumberOfYears],
       CASE
           WHEN DATEADD(YEAR, DATEDIFF(Year, service_date, GETDATE()), service_date) < GETDATE()
           THEN DATEADD(YEAR, DATEDIFF(Year, service_date, GETDATE()) + 1, service_date)
           ELSE DATEADD(YEAR, DATEDIFF(Year, service_date, GETDATE()), service_date)
       END AS [AnniversaryDate]
FROM [PERSON]

[v-piga-msft]

Hi @diskovered ,

I have a little confused about your sceanrio.

Do you want to group your data by years? 

Based on your code above, you may could try IF function to achieve that in Power BI.

If it is convenient, could you share some data sample and your desired output in Power BI  so that we could help further on it?

Best  Regards,

Cherry

[diskovered]

Hi Cherry,

Scenario:

Say today is an [employee's] 5th or 10th or 30 year anniversary. I want employee's name shown in their respective year of service area. Second condition is I don't want their names to stay there forever, I only want it there for 30 days after their anniversary date. eg: (Sorry, I can't conveniently share my pbix) Thanks