cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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 v-ljerr-msft
Microsoft

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

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

19 REPLIES 19
Super User IV
Super User IV

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

Hi @joshcomputer1

 

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.

 

radacad.com/do-you-need-a-date-dimension

 

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

 

 





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

Proud to be a Super User!






Power BI Blog

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

TeamNameGMSMRoleStart DateEnd DateActive?
FirstMichael KayesBobIT Support Agent3/30/20165/25/20171
FirstEnrique KayesBobIT Support Agent12/23/20155/25/20171
FirstJerryKayesBobIT Support Agent10/21/20135/25/20171
FirstJimKayesBobSM7/1/20125/25/20171
        
Highlighted
Microsoft v-ljerr-msft
Microsoft

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

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

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 v-ljerr-msft
Microsoft

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

Hi @joshcomputer1,

 

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. Smiley Happy

 

Regards

rwod Frequent Visitor
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.

 

 

rwod Frequent Visitor
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.

 

 

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.  

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors