topic Re: Number of rows between a start and end date in DAX Commands and Tips

Number of rows between a start and end date

smanderson — Wed, 02 Jun 2021 22:46:45 GMT

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!

Re: Number of rows between a start and end date

amitchandak — Thu, 03 Jun 2021 02:17:56 GMT

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-trend/ba-p/882970

create a separate date and department table

Re: Number of rows between a start and end date

Jihwan_Kim — Thu, 03 Jun 2021 03:01:36 GMT

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

Re: Number of rows between a start and end date

smanderson — Thu, 03 Jun 2021 17:16:13 GMT

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.