This is probably simple, but, as a new Power BI user, I’ve been struggling with this for a couple of days and am hoping to get some advice from the forum. I thought about trying to share everything I tried so far, but I don’t think I could describe it all in a way that anyone could make sense of. Suffice it to say that I have tried and failed many different ways already.
I have a list of employees with the ID numbers and hire dates. The company recognizes every employee on the work anniversary every 5th year. In other words, I am recognized on my 5-year anniversary, then again on my 10-year anniversary, then 15-year, etc.
I need to create a report that allows the user to select a year and (optionally) a quarter resulting in a list of employees who have a recognized anniversary during that quarter. The information displayed in the table should be:
- Anniversary Quarter (ie. Q1, Q2, etc.)
- Anniversary Date
- Anniversary Number (ie. 5, 10, 15, etc.).
The date range included in the report need only include a 5-year period beginning 1/1/2019, but it’s not a problem if a wider range is included. If it is limited to 5 years, that range would need to be adjusted every year to start on 1/1 of the current year.
I have attached a PBIX file with fictitious employee names, IDs, and hire dates.
Thank you in advance for any ideas you may be able to share to get me going in the right direction!
Employee Anniversaries - Demo.pbix (286.9 KB)