Latest Enterprise DNA Initiatives

Calculating Employee Anniversaries

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:

  • Name
  • 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)

1 Like

Hi DaveC,

I have added a PBIX with a possible solution. It is based on a formula that Marco Russo used for calculating birthdays:

Anni M =
VAR Anndate = VALUES(Employees[Hire Date])
RETURN
IF (
NOT ISBLANK ( Anndate );
VAR ThisDay = DATE ( 2019; 10; 15 )
VAR IntAnndate = YEAR ( Anndate ) * 10000 + MONTH ( Anndate ) * 100 + DAY ( Anndate )
VAR IntThisDay = YEAR ( ThisDay ) * 10000 + MONTH ( ThisDay ) * 100 + DAY ( ThisDay )
VAR Ann = QUOTIENT ( IntThisDay - IntAnndate; 10000 )
VAR CheckedAnn = DIVIDE ( Ann; NOT ISBLANK ( Anndate ) )
RETURN
CheckedAnn
)

I have changed the ranges in the datetable to test the solution, but I’m sure you can adjust them yourself.

Employee Anniversaries - Demo D.pbix (618.8 KB)

@DaveC,

:+1: on your initial post. Yours is a perfect example of how to make optimal use of this forum - very clear statement of the problem and the desired outcome, and PBIX attached with all the information needed. This makes it really straightforward for the Enterprise DNA team and other members to provide support.

Also, major kudos on trying and failing in many different ways. That IMO is the only way to truly learn the concepts Sam presents in his videos - it always looks easy when he does it. After you’ve wrestled with a problem for a good while, seeing someone else’s approach/solution will be much more meaningful/beneficial.

I’ll have a suggested approach for you shortly.

  • Brian

@DaveC,

OK, as I was posting above @uriah1977 already posted a solution, proving my point about how easy you made it to provide support…:smile:

Here are some other forum posts addressing similar issues that you may also find helpful:

  • Brian
1 Like

@uriah1977 and @BrianJ, Thanks for the help! Based on what I gleaned from your responses, I’ve updated the PBIX file so that it shows exactly what I’m looking for… BUT it only works for celebrated anniversaries in the current quarter because the calculations are based off of TODAY(). I need to be able to choose at least a year from slicer and have the table show the celebrated anniversaries for that year. As before, I tried several things but was unsuccessful.

2019-11-11_11-49-42

Getting closer… Any thoughts?

Employee Anniversaries - Demo.pbix (1013.9 KB)

@DaveC,

You’re making good progress, but I think the problem is that you have no relationship between the date table and the employee table, so even though you’re harvesting the slicer value, it’s not actually doing anything in terms of filtering your employee table. Once you define an active relationship, the slicer values chosen will dynamically change your visual.

In terms of slicers, you may want to take a look at the Hierarchy Slicer custom visual. It handles the type of year/quarter selection you’re looking for really cleanly (see screenshot below).

Hope this is helpful.

  • Brian

image

I was actually looking for the hierarchy slicer earlier today, but my company blocks the use of any custom visual that is not Power BI Certified, and I did not see it on the list of certified visuals.

I’ll work on the date relationship later this afternoon.

Thanks!

Well, I ended up getting it to do exactly what I wanted, but the solution was much more involved than I thought it would be. I’d be interested in knowing if there is a simpler way to achieve the same outcome.

Basically, I ended up duplicating my “Employees” table multiple times" and adding an “Anniversary Date” column to each. In one table, I calculated everyone’s 5-year anniversary date, in the next their 10-year date, etc. up to 50-year anniversaries. Then I appended them all into one table with a UNION so that I have one table that has all anniversaries that matter in it without having a bunch of others that don’t matter.

As I said above, if anyone has ideas for a simpler way to do it and still get the same output, I’d love to hear it because I’m a complete beginner with Power BI and DAX, and I don’t know when I’m doing things efficiently or when there are much better ways.

Employee Anniversaries - Demo.pbix (1.2 MB)

@DaveC,

First off, kudos for getting to a solution that works. For a complete beginner in DAX, that demonstrates a good use of the less commonly used UNION function.

But there is a much easier way. Given the static nature of anniversary dates, these are perfect to calculate using Power Query. Also, generally speaking from a data modeling standpoint, you want to build “down” (more rows), not “across” (more columns).

Using the custom column function in M below, you can quickly calculate each anniversary date in PQ.

= Table.AddColumn(#“Reordered Columns”, “5”, each Date.AddYears([Hire Date], 5))

If you use the anniversary number as your column header, when you unpivot the anniversary date columns, you’ll end up with exactly the desired structure.

image

In the attached solution file, you can go into PQ and go through the steps I took to transform the Anniversary Dates table.

Also, note that you will need a MUCH bigger date table than the one you have posted - starting at the earliest hire date to the latest anniversary date.

In the solution file I posted below I just added the 5 and 10 year anniversaries. I would encourage you to start from scratch, duplicate your Employee table and try to replicate this through PQ adding all your desired anniversaries.

If you have any problems, just give a shout.

I hope this is helpful.

@BrianJ, thanks for all the coaching! I will take a look at your file and no doubt learn lots from it. Reading your comments, however, remind me that one thing I never explained is that there is an extra wrinkle to my real data that is not reflected in my fictitious sample file. I am pulling the employee table from an HR database that has all employees in the company (~15,000), but this report is only for one department of ~200 people. I am using PATH() and PATHCONTAINS() to isolate the people in the target department. That is why all my calculation of anniversary dates was happening in DAX instead of PowerQuery.

Having said that, I guess I could always do the PQ transformations for all employees, and still restrict the final list the same way I am currently doing it after all the calculations have been made instead of before. I don’t know which method would result in better response times or if it would even make a difference for a table of 15,000 rows.

@DaveC,

If you choose to go the Power Query route, it likely will chew through your 15,000 records without even breaking a sweat, but alternatively in the first steps of your transformation you could filter the records down to the relevant ~200 before calculating anniversary dates and unpivoting columns.

  • Brian

If I’m not mistaken, PATH() and it’s related functions are only available in DAX–not in Power Query, right? I don’t know how to filter down to the relevant names without those functions because the only thing they have in common is the same VP somewhere in their management chain.

@DaveC,

Very good point. It is possible to replicate the functionality of PATH() in PQ through custom functions, but it ain’t easy.

Practically speaking though, you’re probably better off processing the whole dataset in PQ, and then filtering it down to the relevant records via DAX PATH() (or alternatively, going with a DAX-only solution). Personally, I like the former because it’s so easy to shape the Anniversary fact table into an excellent structure for analysis, but that would certainly also be doable in DAX - just a bit more difficult.

This is part of what I find fascinating about Power BI - for almost any problem there are multiple valid ways of ultimately accomplishing the same thing.

  • Brian