cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kaledjeff Regular Visitor
Regular Visitor

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

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

Re: Number of employee between two dates

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!

Phil_Seamark Super Contributor
Super Contributor

Re: Number of employee between two dates

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!

6 REPLIES 6
Phil_Seamark Super Contributor
Super Contributor

Re: Number of employee between two dates

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!

Highlighted
KeithChu Regular Visitor
Regular Visitor

Re: Number of employee between two dates

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

 

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

kaledjeff Regular Visitor
Regular Visitor

Re: Number of employee between two dates

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.

Phil_Seamark Super Contributor
Super Contributor

Re: Number of employee between two dates

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!

kaledjeff Regular Visitor
Regular Visitor

Re: Number of employee between two dates

Thank you, @Phil_Seamark. It works great.

 

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

 

Thank you both for your contibrution. 

 

BR

kristen Regular Visitor
Regular Visitor

Re: Number of employee between two dates

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