Number of rows between a start and end date

[smanderson]

I'm starting with a Data Source Structured like this:

I intend to produce a table/viz showing how many employees were employed on any given date by department like this:

I've explored some other help topics and it seems like this invovles filter context and related tables, but I'm not sure how to crack it. Thanks in advance!

[Jihwan_Kim · Accepted solution]

Hi, @smanderson 

Please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file.

Apple employees =

CALCULATE (

COUNTROWS ( employees ),

employees[department] = "apple",

FILTER (

employees,

employees[Hire Date] <= MAX ( Dates[Date] )

&& OR(employees[Fire Date] >= MIN ( Dates[Date] ), ISBLANK(employees[Fire Date]))

)

)

Banana employees =

CALCULATE (

COUNTROWS ( employees ),

employees[department] = "banana",

FILTER (

employees,

employees[Hire Date] <= MAX ( Dates[Date] )

&& OR( employees[Fire Date] >= MIN ( Dates[Date] ), ISBLANK(employees[Fire Date]))

)

)

Total employees =

CALCULATE (

COUNTROWS ( employees ),

FILTER (

employees,

employees[Hire Date] <= MAX ( Dates[Date] )

&& OR(employees[Fire Date] >= MIN ( Dates[Date] ), ISBLANK(employees[Fire Date]))

)

)

https://www.dropbox.com/s/eg4yx3nqhs9bgsf/smanderson.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[amitchandak]

@smanderson , usually we do it with help from date table as discussed in my blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

create a separate date and department table

[smanderson]

Great Blog post!  I must be doing something wrong, The "current employees" measure seemed to get stuck on the first day's total for me.