Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors