cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
Microsoft

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
Microsoft

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors