cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## Calculate employees available per day

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.

4 REPLIES 4
Community Support

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.

Frequent Visitor

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?
Super User
Frequent Visitor

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors