Employee Anniversary

[Edgar89]

Hi all,

in the community are several similar questions - but no one works for me by now. I want to have the anniversarys of employees. (5 years, 10 years, 15 years, 20 years, 25 years, 30 years)

Here is an example of my data (I have a date table as well)

If I choose the filter "May 2022" I would like to have this output:

Employee "D" should not be shown (6years..)

If I choose "November 2022" the output should look like following:

Any ideas?

Thanks so far 🙂

[AlexanderPrime · Accepted solution]

In Power Query. Create Custom Columns for each anniversary date using the below code

= Table.AddColumn(#"Changed Type", "5yr", each Date.AddYears([Employment Date],5))

Change the 5 for your additional landmark year

When this is done, select all your anniversary date columns in Power Query. Then select "Unpivot Columns".

You should then see it merges the 5/10/15/20/25/30 year columns into two columns called "Attribute" and "Value"

Attribute will be the anniversary type and Value will be the date so you can then rename and use those as your lookup on the table above .

But also make sure the generated "Value" column has it's data type changed to Date, you may need to do this yourself as it is likely not automatically done. 

Then to make a filter for Month/Year. In DAX. Make the following column (not measure!):

MonthYear = FORMAT(Table1[Value],"mmm-yyyy")

This will give you a "month and year lookup" you can add to a relationship to a calendar table and then use as a "select month/year" filter so you can then get your intended result.