Getting a Count of Something on the Last Day of Each Month

Recently, I received a request to plot a count of our employees as of the last day of each month. A reasonable request for sure; however, Tableau didn’t offer a quick solution. I could have written the function in SQL, but thought I’d figure it out in Tableau as well.

Here’s how I got there:

My initial employee headcount plot was counting all unique Employee IDs for each month. On the surface, this gives the appearance of a count of employees as of the 1st day of every month. That appearance was deceiving though. In actuality, it was counting every unique Employee ID found in each month, even if they were terminated before the end of the month.

What I needed to do was identify the last day of every month, and then only count the unique Employee IDs on those particular dates, even if I was plotting monthly headcount.

First I created a calculated field that identifies the last day of every month:

DATEADD(‘day’,-1,DATEADD(‘month’,1,DATETRUNC(‘month’,[Date])))

Then I needed to create another calculated field that compares the last day of every month with the termination date of each employee. Here’s that:

[Last Day of Month] >= [Termination Date]

I put that calculated field on the Filters shelf, and set the value to ‘False’. This meant that if an employee was terminated prior to the end of the month, we would not include them in the count of employees we are making on the last day of the month. This is the key. This removes all those employees that didn’t stay active until the last day of the month.

If you are not working with Employee Headcounts, then the [Termination Date] field can be replaced with any other kind of date that denotes that an item is no longer part of the pool that should be counted.

Hope this was helpful.

Leave a Reply

Your email address will not be published. Required fields are marked *