cancel
Showing results for
Did you mean:
kaledjeff 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

Re: Number of employee between two dates

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')
)          ``` and when plotted you get this.. Proud to be a Datanaut!

Highlighted
Phil_Seamark Super Contributor

Re: Number of employee between two dates

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')
)    ```

Proud to be a Datanaut!

6 REPLIES 6
Phil_Seamark Super Contributor

Re: Number of employee between two dates

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')
)          ``` and when plotted you get this.. Proud to be a Datanaut!

KeithChu 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

Re: Number of employee between two dates

Hello @Phil_Seamark,

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
Phil_Seamark Super Contributor

Re: Number of employee between two dates

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')
)    ```

Proud to be a Datanaut!

kaledjeff 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

Re: Number of employee between two dates

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