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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kaledjeff
Advocate I
Advocate I

Number of employee between two dates

Hello DAX Experts,

 

I'm trying to figure out a formula that calculate the number of employee for each day between two dates. My table looks something like this:

 

 

I have a calendar table and I need know for each day how many people we have in IT, HR, Fianance, ...etc.

 

I very much appreacite your help. 

2 ACCEPTED SOLUTIONS
Phil_Seamark
Microsoft Employee
Microsoft Employee

HI @kaledjeff

 

This calculated table uses an embedded date table

 

Table = 
VAR ExpandedTable =     
    GENERATE(
        CALENDAR(DATE(2017,1,1),TODAY()),
        FILTER(
            'Employees',
            [Date]>='Employees'[Start Date] &&
            [Date]< IF(ISBLANK('Employees'[End Date]),TODAY(),'Employees'[End Date])
            )
            )
            
RETURN 
    SUMMARIZE(  
        ExpandedTable,
        [Date] , 
        "Count",COUNTROWS('Employees')
        )          

image.png

 

and when plotted you get this..

 

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Hi @kaledjeff

 

Well done on getting it to work with your date table

 

If you add [Department] to the final SUMMARIZE statement, you can then use this field as a filter

 

RETURN 
    SUMMARIZE(  
        ExpandedTable,
[Date] ,
[Department] , "Count",COUNTROWS('Employees') )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

8 REPLIES 8
KeithChu
Helper I
Helper I

I suppose you can add a calculate column on this table.

 

= COUNTROWS(FILTER('Employee'), [JoinDate] >=  [Start Date] && [JoinDate] <= [End Date] && [Employee] = [Employee] && [Department] = [Department])

Phil_Seamark
Microsoft Employee
Microsoft Employee

HI @kaledjeff

 

This calculated table uses an embedded date table

 

Table = 
VAR ExpandedTable =     
    GENERATE(
        CALENDAR(DATE(2017,1,1),TODAY()),
        FILTER(
            'Employees',
            [Date]>='Employees'[Start Date] &&
            [Date]< IF(ISBLANK('Employees'[End Date]),TODAY(),'Employees'[End Date])
            )
            )
            
RETURN 
    SUMMARIZE(  
        ExpandedTable,
        [Date] , 
        "Count",COUNTROWS('Employees')
        )          

image.png

 

and when plotted you get this..

 

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

Hi,

By expanding it can quickly become a large table if the dataset (like mine) consist of approx 50.000 rows each year for 5+ years. 
Is it possible to get the same output using a measure? 

Appreciate thoughts around this approach. 

Anonymous
Not applicable

Hi,

 

I believe that I have the solution for how to create this as a measure. See code below

 

Count = 
VAR _EndOfMonth = 
SELECTEDVALUE( DateTable[EndOfMonth] ) /*Assuming a custom table with a column "EndOfMonth" has been created*/
RETURN
CALCULATE(
    DISTINCTCOUNT( Employees[Employee] ),
    Filter( 
        Employees,
        Employees[Start Date] <= _EndOfMonth
        && Employees[End Date] >= _EndOfMonth //Assuming no blanks in End Date
    )
)

 

Hello @Phil_Seamark,

 

Thank you for your quick reply.

 

I tried your method but unfornatluty I didn't get the same reults.

 

I used your method on the calendar table that I have and it worked great except I cannot filter by a department. I understand that I can create a function for department but It'll take a lot to calculate since I have many deprtment. Is there a simpler way?

 

Thanks.

Hi @kaledjeff

 

Well done on getting it to work with your date table

 

If you add [Department] to the final SUMMARIZE statement, you can then use this field as a filter

 

RETURN 
    SUMMARIZE(  
        ExpandedTable,
[Date] ,
[Department] , "Count",COUNTROWS('Employees') )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

I have been trying to figure this out FOREVER. Thank you so much!

Thank you, @Phil_Seamark. It works great.

 

@KeithChu I'll try your formula and let you know.

 

Thank you both for your contibrution. 

 

BR

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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