cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
imy Helper I
Helper I

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 =
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
JRe5777 Helper I
Helper I

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:

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




Super User IV
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/




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

Proud to be a Super User!






Power BI Blog
BSacchini
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
 
 
Can anyone please help!!!
JRe5777 Helper I
Helper I

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

maggigfrench
Regular 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.

 

JRe5777 Helper I
Helper I

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

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

 

Highlighted
justincordasco
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? 

gmalekar
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

louisvp Helper II
Helper II

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors