cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper 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

Accepted Solutions
Highlighted
Microsoft
Microsoft

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!

View solution in original post

Highlighted
Microsoft
Microsoft

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!

View solution in original post

6 REPLIES 6
Highlighted
Microsoft
Microsoft

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!

View solution in original post

Highlighted
Helper I
Helper I

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])

Highlighted
Helper I
Helper I

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.

Highlighted
Microsoft
Microsoft

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!

View solution in original post

Highlighted
Helper I
Helper I

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

Highlighted
Advocate I
Advocate I

Re: Number of employee between two dates

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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors