Showing results for 
Search instead for 
Did you mean: 
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 =
    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.



Community Support
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.

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
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 =
    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

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors