topic Re: Calculate employees available per day in DAX Commands and Tips

Calculate employees available per day

DJTentman — Tue, 06 Dec 2022 21:10:36 GMT

I want to create a table with how many employees are available to work each day. I have a table with all the dates:

Calendar Relative = CALENDAR( TODAY()-3650, TODAY()+3650 )

I have another table with all employees listed as well as their starting date and ending date (10 years in the future if they are employed ongoing)

I have a column with the following DAX, but I know this isn't quite what I need.

Available Employees =

CALCULATE (

COUNT('Employees'[First Name]),

'Employees'[Start Date] < 'Calendar Relative'[Date] || 'Employees'[Last Day Filled] > 'Calendar Relative'[Date]

Can anyone help me accomplish this. I'm not very skilled with DAX.

Re: Calculate employees available per day

amitchandak — Wed, 07 Dec 2022 02:52:00 GMT

DJTentman , refer to the blog or attached files

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Re: Calculate employees available per day

DJTentman — Wed, 07 Dec 2022 15:01:48 GMT

Thanks, that's been helpful. So, I'm trying to create the number of employee/days per week for each date in the list. This is the DAX I'm using:

Available Employees =

CALCULATE (

SUM('Employees'[Days per week]),

FILter(Employees,'Employees'[Start Date] < [Date] || 'Employees'[Last Day Filled] > [Date]

))

However, this creates a count that is the same for every day. I'm not quite sure why.

Re: Calculate employees available per day

v-zhangti — Mon, 12 Dec 2022 06:15:33 GMT

Hi, DJTentman

You can try the following methods.
Sample data:

Measure = CALCULATE ( COUNT ( 'Table'[Employee ID] ), FILTER ( ALL ( 'Table' ), [Start Date] <= SELECTEDVALUE ( 'Date'[Date] ) && [End Date] >= SELECTEDVALUE ( 'Date'[Date] ) ) ) + CALCULATE ( COUNT ( 'Table'[Employee ID] ), FILTER ( ALL ( 'Table' ), [Start Date] <= SELECTEDVALUE ( 'Date'[Date] ) && [End Date] = BLANK () ) )

Hope this method helps you.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Calculate employees available per day

DJTentman — Mon, 12 Dec 2022 13:41:30 GMT

This makes a lot of sense. Thanks. I ended up with this formula:

Available =

CALCULATE ( COUNT ( 'Employees'[ID] ),

FILTER ( ALL ( 'Employees' ),

[Start Date] <= SELECTEDVALUE ( 'Calendar Relative'[Date] )

&& [Last Day Filled] >= SELECTEDVALUE ( 'Calendar Relative'[Date] ) ) )

I don't need to worry about blank quit dates because it automatically fills in a date 10 years in the future if it is blank.

However, I come up with blank values for every day with this formula. What am I doing wrong?