cancel
Showing results for
Did you mean:
Regular Visitor

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

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 =
RETURN
CALCULATE (
FILTER (
)
)

I hope I am explaining my self,
Imy
Regular Visitor

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

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:

Can we solve this?

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

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

Super User IV

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

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/

Proud to be a Super User!

Power BI Blog
Frequent Visitor

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

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

Regular Visitor

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

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

Visitor

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

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.

Regular Visitor

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

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

Frequent Visitor

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

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

Frequent Visitor

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

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

Highlighted
Regular Visitor

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

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

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!