Skip to main content
cancel
Showing results for 
Search instead 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

Reply
DJTentman
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
v-zhangti
Community Support
Community Support

Hi, @DJTentman 

 

You can try the following methods.
Sample data:

vzhangti_0-1670825559046.png

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

vzhangti_1-1670825671624.png

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.

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?
amitchandak
Super User
Super User

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.

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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