Calculate employees available per day

[DJTentman]

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.

[v-zhangti]

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.

[DJTentman]

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]

@DJTentman , refer to the blog or attached files

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[DJTentman]

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.