cancel
Showing results for
Did you mean:
Member

## 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

Accepted Solutions
Highlighted
Microsoft

## Re: Calculating a monthly employee count from a start and end date range

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
)
)
```

Here is the sample pbix file for your reference.

Regards

19 REPLIES 19
Super User IV

## Re: Calculating a monthly employee count from a start and end date range

Do you have some sample data, which will make it easier?

You could so a measure with the following syntax below, but you would require to have a Date Table in order for this to work. Where you create a relationship your data to the Date table.

```Total Employees =
VAR EarliestDate = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED())
RETURN CALCULATE(DISTINCTCOUNT('tblEmployees'[colActive]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date] <= MAX('Calendar'[Date]) && 'Calendar'[Date] >= EarliestDate))```

Proud to be a Super User!

Power BI Blog
Member

## Re: Calculating a monthly employee count from a start and end date range

 Team Name GM SM Role Start Date End Date Active? First Michael Kayes Bob IT Support Agent 3/30/2016 5/25/2017 1 First Enrique Kayes Bob IT Support Agent 12/23/2015 5/25/2017 1 First Jerry Kayes Bob IT Support Agent 10/21/2013 5/25/2017 1 First Jim Kayes Bob SM 7/1/2012 5/25/2017 1
Highlighted
Microsoft

## Re: Calculating a monthly employee count from a start and end date range

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
)
)
```

Here is the sample pbix file for your reference.

Regards

Member

## Re: Calculating a monthly employee count from a start and end date range

How do I add a relationship to the date calendar? Relate the start and end dates columns to it? I already have months and years and week numbers columns in most of my excel docs and would like to move away from that.
Microsoft

## Re: Calculating a monthly employee count from a start and end date range

The relationship to the date calendar is not needed here.

Could you share a sample pbix file, so that we can better assist on this issue? You can upload it to OneDrive or Dropbox and post the link here, or sent it to me in private message. Do mask sensitive data before uploading.

Regards

Frequent Visitor

## Re: Calculating a monthly employee count from a start and end date range

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.

Frequent Visitor

## Re: Calculating a monthly employee count from a start and end date range

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.

Member

## Re: Calculating a monthly employee count from a start and end date range

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.

Visitor

## Re: Calculating a monthly employee count from a start and end date range

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

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!