Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
joshcomputer1
Helper V
Helper V

Calculating a monthly employee count from a start and end date range

Hi there, 

I just need some help organizing how to do this.  I have a table that displays the employees' names, title, start date, end date and "active?".  The active column just shows whether they are an active employee today or not.  

 

What I am wanting is to be able to show (based on the start date and end date columns how many employees we had for each month.  The range right now only goes back to Jan. 1, but want to get started on this. Any help is appreciated.

 

**I thought about just having a column for each month with a 1 or 0 if they were active during the month.  This would work, except there is additional columns added for each month and this is not maintenance free (at least not how I had it).  I am familiar with excel but new to power BI. 

 

table= tblEmployees

column= EmpName

column= colStartDate

column= colEndDate

column= colActive?

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

Hi @joshcomputer1,

 

If I understand you correctly, you should be able to follow steps below to get the monthly employee count from a start and end date range.

 

1. Add an individual Calendar table if you don't have one yet.

Date = CALENDARAUTO()

2. Use the formula below to create a measure, and show the measure on the Chart visual with Date[Date] column as Axis.

Count of Active Employee =
VAR currentDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            Table1,
            ( Table1[Start Date] <= currentDate
                && Table1[End Date] >= currentDate )
                && Table1[Active?] = 1
        )
    )

r4.PNG

Here is the sample pbix file for your reference. Smiley Happy

 

Regards

View solution in original post

24 REPLIES 24
Anonymous
Not applicable

@joshcomputer1 If some of my "End dates" are empty, which means the employee is obviously still with us, does the above calculation bring that into consideration? or do I need to add in an additional line to also include those items in the counts for active employees?

Anonymous
Not applicable

Thanks @joshcomputer1 for this solution! I've struggled to find a way to do this, and your steps and PBIX file was excellent

louisvp
Helper II
Helper II

Dear Community, I want to create a simular table but then in Power Query using the m Query language. Could someone help me with this? When I use the DAX code in Power BI Desktop, my Desktop file beocomes very slowly: Running total size = CALCULATE ( COUNTA (Data [StaffLevelName]), FILTER ( ALL (Data) , Data [FiscalStartYear] <= MAX (Data [FiscalStartYear])) ) Thank you very much. Kind regards Louis van Paassen

Hi everyone,

 

What happens if an employee is re-hired with his same employee number? What would change? @louisvp @joshcomputer1 @Anonymous @Anonymous @justincordasco @JRe5777 

BSacchini
Frequent Visitor

Hello,

 

I am trying to figure out how to calculate active employee count for different dates. I have two table, Employee and Date. 

In the employee table I have columns with [DateIn] for when the employee started at the company and [DateOut] for when the employee left the company. For all current employees [DateOut] its blank. 

 

I am using the following: 

CountofActiveEmployee1 =
VAR MaxCurrentDate = MAX('Date'[Date])
RETURN
CALCULATE(
COUNTROWS(Employee);
FILTER(
Employee;
(Employee[DateIn]<= MaxCurrentDate
&& Employee[DateOut] >= BLANK())
)
)

 

I am also trying with this:

CountofActiveEmployee =
VAR MaxCurrentDate = MAX('Date'[Date])
var MinCurrentDate = MIN('Date'[Date])
RETURN
CALCULATE(
COUNTROWS(Employee);
FILTER(
Employee;
(Employee[DateIn]<= MaxCurrentDate
&& Employee[DateOut] >= MinCurrentDate)
)
)
 
In both measures I get the same results (except for the Grand Total), but very oddly (to me) what I am getting is the count of inactive employees filter by the date they got out. See picture
 
 
Can anyone please help!!!

Hello @BSacchini I think maybe is the way you are using the BLANK condition.

I suggest you try this measure that combines the solution on this thread with a blank cases' thread, as it worked for me.

 

So first, make sure your date table ends at TODAY()

The formula I used for the date table was

Dates=CALENDAR(DATE(1950,1,1),TODAY())

I did this instead of CALENDARAUTO() because with this I would get the maximum date at 31/12/2019, which means it would be the active employee for the end of the year, and I wanted it dynamically for months or even days (so I can get the real number of ative employees every day I check the report)

 

Then on the measure for the employee table you can do:

Employees = 
VAR currentDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT(Employee[Employee Number]),
        Employee[DateIn] <= currentDate,
        OR(ISBLANK(Employee[DateOut] ),
            Employee[DateOut]  > currentDate)
    )

Then on a matrix or bar chart you use the Dates table for filtering and the measure for values and you can get the number of active employees per month, for example.

 

Let me know if this worked for you.

Best Regards

v-ljerr-msft
Employee
Employee

Hi @joshcomputer1,

 

If I understand you correctly, you should be able to follow steps below to get the monthly employee count from a start and end date range.

 

1. Add an individual Calendar table if you don't have one yet.

Date = CALENDARAUTO()

2. Use the formula below to create a measure, and show the measure on the Chart visual with Date[Date] column as Axis.

Count of Active Employee =
VAR currentDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            Table1,
            ( Table1[Start Date] <= currentDate
                && Table1[End Date] >= currentDate )
                && Table1[Active?] = 1
        )
    )

r4.PNG

Here is the sample pbix file for your reference. Smiley Happy

 

Regards

Can you revise this to calculate for a 3 month rolling average?

Thank you, thank you, thank you, so much for this fix. 

I've been having a helluva time tracking down a data request similiar to my issue, along with viable, applicable solution, and came across this.  Worked perfectly when applied to my project.  Cheers!

Anonymous
Not applicable

Hey this is awesome can you explain the working of this dax measure so as to understand what is happening under the hood

I'm needing to do the same thing, but am getting an error with this solution and perhaps it's becuase all of my "active" employees don't have "leave dates" - that field is null. How can I account for that??

 

For reference, here's the formula i'm using with my own references: 

Count of Active Employee3 = VAR currentDate = MAX('Date Table'[Date]) RETURN CALCULATE(COUNTROWS('Team List'), FILTER('Team List', ('Team List'[Start Date] <= currentDate && 'Team List'[Leave Date] >= currentDate) && 'Team List'[Status2] = 1))
 
Likewise, here's the error I'm getting on the visual:  

MdxScript(Model) (6, 236) Calculation error in measure 'Team List'[Count of Active Employee3]: DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

 

Hey @maggigfrench 

Is your [Status2] column value type text or integer? It may be that you need to put "1"

 

Also, to take into account the null leave dates you need to use the ISBLANK function.

 

I would suggest using the calculate function like this:

 

Count of Active Employee3 =
VAR currentDate = MAX('Date Table'[Date])
RETURN
CALCULATE (
DISTINCTCOUNT('Team List' [ Employee ID ? ]),
'Team List'[Start Date] <= currentDate ,
OR(ISBLANK('Team List'[Leave Date]),'Team List'[Leave Date]> currentDate) ,
'Team List'[Status2] = 1
)

 

Let me know how this worked out for you

 

Dear v-ljerr-msft

 

Looking at this tread, this is exactly what I need.

However, using your formula bring me the total employee today, rather than per month, in my case I get 27 each month for all the years (which is the total employees today), what am I doing wrong? By the way, I must mention that I dont have an end date for each employee as they are all active. So your [End Date] = Today() in my table and your 'Date' [Date] = Today() in my table.

Count of Active Employee =
VAR currentDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            Table1,
            ( Table1[Start Date] <= currentDate
                && Table1[End Date] >= currentDate )
                && Table1[Active?] = 1
        )
    )

My formula looks like this:

Count of Active Employee =
VAR currentDate =
MAX ( Headcount[Today] )
RETURN
CALCULATE (
COUNTROWS(Headcount),
FILTER (
Headcount,
( Headcount[Hire Date] <= currentDate
&& Headcount[Today] >= currentDate )
&& Headcount[Active] = 1
)
)
 
I hope I am explaining my self,
Imy

I have the same problem and I think is because my max (date) is not resulting in the last day of each month.

My calendarauto() last date is 31/12/2999 (or 31/12/2019 if I were to put the end date as blank).

Because of that I also have the same number for every month:

image.png

 

 

 

 

image.png

 

 

Can we solve this?

I ended up solving this by changing the date table to:

CALENDAR(DATE(1950,1,1),TODAY())




I'm getting the same total for each period, as well. Changing the date formula does not make a difference. Thoughts? 

Hi there

I would suggest creating a custom date table, where you can limit it to stop at the last date you have got data.

This will also allow you to be able to create custom columns as per your requirements.

Here is my blog post on how to easily create a date table: https://www.fourmoo.com/2016/09/07/create-dynamic-periods-for-fiscal-or-calendar-dates-in-power-bi/




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Instead of COUNTROWS we should be using DISTINCTCOUNT here and referencing Employee Name

Question related to this post...

 

I've copied the formula, but I'm not sure what the 

Table1[Active?] = 1

is for, and what I might need to add in terms of calculating this column of data in order to complete the count you have listed below.  My current file has an active column, but it is only indicating if they are currently active (not active during the variable time periods on a monthly basis) and that is a static amount not variable pending the date hierarchy in the visual.

 

 

Table1[Active?] is referring to Table 1 and the column named "Active?".  So if they have a 1, they are currently active. We actually use a filter in the table to only show active team members.  This sounds like what you are using. 

 

The real magic here is the begin and end date fields.  The DAX formula listed references those dates and then computes if there were team members active during certain months.  

Question related to this post...

 

I've copied the formula, but I'm not sure what the 

Table1[Active?] = 1

is for, and what I might need to add in terms of calculating this column of data in order to complete the count you have listed below.  My current file has an active column, but it is only indicating if they are currently active (not active during the variable time periods on a monthly basis) and that is a static amount not variable pending the date hierarchy in the visual.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.