cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## active employee count per month

Hi,

i have a table with columns as "project", "name", "start date" and "end date".

1. i wanted to count the number of employees who has joined the project and also who has left the project per month.

Exmaple: if 10 employees joined the project in jan and 8 in Feb and 12 in march, and 3 left the project in jan, 4 in feb, and 5 in March. i want the result to be shown as, number of employes in jan= 10-3=7, Feb= 7(from jan)+8-4=11, march= 11(from Feb)+12-5=18.

for this i have calculated cumulative count of employees who has joined the project:

startcount= CALCULATE(COUNTROWS('table'),FILTER(ALLSELECTED('table'),'table'[Start date]<=MAX('table'[Start date])))

and calculated number of employees who has left:
Endcount = CALCULATE(COUNTROWS('table'),USERELATIONSHIP('table'[End Date'],'Date'[Date]))

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super Contributor

## Re: active employee count per month

Based on my test, i've added index column in query editor for date table. Below are measures for your reference.

`Startcount = CALCULATE(COUNTROWS('Sample'))`
`Endcount = CALCULATE(COUNTROWS('Sample'),USERELATIONSHIP('Sample'[End Date],'Date'[Date]))`
`Leftcount = [Startcount]-[Endcount]`
```Final =
VAR a =
SUMMARIZE (
FILTER ( ALL ( 'Date' ), 'Date'[Index] <= MAX ( 'Date'[Index] ) ),
'Date'[Index],
'Date'[Month],
"b", [Leftcount]
)
RETURN
SUMX ( a, [Leftcount] )```

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super Contributor

## Re: active employee count per month

After you have loaded your table ( named Data ) into Power BI, create a new table ( named DataModified) with the following DAX Query:

```DataModified =
SELECTCOLUMNS(
GENERATE(
Data,
VAR StartDate = Data[Start Date]
VAR EndDate = Data[End Date]
RETURN
CALENDAR( StartDate, EndDate - 1 )
),
"Project", Data[project],
"Name", Data[Name],
"Date", [Date]
)```

Afterwards create the calendar, projects and names dimensions:

Names = ALLNOBLANKROW( DataModified[Name] )

Projects = ALLNOBLANKROW( DataModified[Project] )

```Calendar =
CALENDAR( DATE( 2017, 1, 1 ), DATE( 2019, 12, 31 ) ),
"Month", MONTH( [Date] ),
"Month Name", FORMAT( [Date], "MMMM" ),
"Year", YEAR( [Date] ),
"Month Year Name", FORMAT( [Date], "mmm yyyy" ),
"Month Year Number", YEAR( [Date] ) * 100 + MONTH( [Date] )
)```
Then create the relationships:

Then add a mtrix visual, drop the column 'Month year Name' from the Calendar table into the rows section and this measure into the values sections:

```Active Employees =
CALCULATE(
COUNTROWS( DataModified ),
LASTDATE( 'Calendar'[Date] )
)```

Proud to be a Datanaut!

8 REPLIES 8
Super User

## Re: active employee count per month

Sounds like you need a running total, there is a Quick Measure that you can use as a basis for that. Also, take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

Proud to be a Datanaut!

Super Contributor

## Re: active employee count per month

@vinay063

Would you be able to post a sample data along with expected results ?

Proud to be a Datanaut!

Frequent Visitor

## Re: active employee count per month

Please fins the below sample data with results required.

project Name Start date End Date
1 Ravi 29-10-2018 31-12-2018
1 akash 29-10-2018 31-12-2018
1 vinay 29-10-2018 31-12-2018
1 nag 29-10-2018 31-12-2018
1 venkata 29-10-2018 31-12-2018
1 suma 12-11-2018 31-12-2018
3 rati 29-10-2018 31-12-2018
3 laxmi 12-11-2018 31-12-2018
3 lucky 03-12-2018 31-12-2018
3 hema 03-12-2018 31-12-2018
3 anju 12-11-2018 31-12-2018
3 dev 03-12-2018 31-12-2018
3 devid 29-10-2018 31-12-2018
3 rahul 29-10-2018 31-12-2018
3 kahlid 29-10-2018 31-12-2018
3 john 29-10-2018 31-12-2018
3 cristi 12-11-2018 21-12-2018
3 ali 01-11-2018 18-01-2019
3 suhas 01-11-2018 18-01-2019
1 veer 01-11-2018 18-01-2019
2 pandu 01-11-2018 18-01-2019
2 kishan 22-10-2018 15-02-2019
2 surab 22-10-2018 15-02-2019
2 mohan 22-10-2018 15-02-2019
2 das 03-09-2018 29-03-2019
2 tomy 03-09-2018 29-03-2019
2 tony 03-09-2018 29-03-2019
2 sunil 03-09-2018 29-03-2019
2 sam 03-09-2018 29-03-2019
2 suraj 03-09-2018 29-03-2019

Results:

1)
Month No.of Emp Joined
Sep-18       6
Oct-18        13
Nov-18       8
Dec-18        3
Jan-19      0
Feb-19       0
Mar-19         0

Month No.of Emp Left
Sep-18        0
Oct-18         0
Nov-18       0
Dec-18        17
Jan-19       4
Feb-19      3
Mar-19        6

3)

Month Final Result     logic
Sep-18    6                   6-0
Oct-18   19                  6+13-0
Nov-18   27                 19+8-0
Dec-18   13                  27+3-17
Jan-19    9                    13+0-4
Feb-19   6                     9+0-3
Mar-19  0                      6+0-6

Logic is just for reference.

Super Contributor

## Re: active employee count per month

Once you have loaded your table (named Data) into Power BI, generate a new table (named DataModified) using the following DAX Query:

```DataModified =
SELECTCOLUMNS(
GENERATE(
Data,
VAR StartDate = Data[Start Date]
VAR EndDate = Data[End Date]
RETURN
CALENDAR( StartDate, EndDate - 1 )
),
"Project", Data[project],
"Name", Data[Name],
"Date", [Date]
)```

Afterwards, create a Date dimension, Project dimension and Names dimension tables:

Names = ALLNOBLANKROW( DataModified[Name] )

Projects = ALLNOBLANKROW( DataModified[Project] )

```Calendar =
CALENDAR( DATE( 2017, 1, 1 ), DATE( 2019, 12, 31 ) ),
"Month", MONTH( [Date] ),
"Month Name", FORMAT( [Date], "MMMM" ),
"Year", YEAR( [Date] ),
"Month Year Name", FORMAT( [Date], "mmm yyyy" ),
"Month Year Number", YEAR( [Date] ) * 100 + MONTH( [Date] )
)```

```Active Employees =
CALCULATE(
COUNTROWS( DataModified ),
LASTDATE( 'Calendar'[Date] )
)```

Create a new matrix visual, drop the 'Month Year Name' column from the calendar table on the rows section and the measure on the values section:

Proud to be a Datanaut!

Super Contributor

## Re: active employee count per month

Based on my test, i've added index column in query editor for date table. Below are measures for your reference.

`Startcount = CALCULATE(COUNTROWS('Sample'))`
`Endcount = CALCULATE(COUNTROWS('Sample'),USERELATIONSHIP('Sample'[End Date],'Date'[Date]))`
`Leftcount = [Startcount]-[Endcount]`
```Final =
VAR a =
SUMMARIZE (
FILTER ( ALL ( 'Date' ), 'Date'[Index] <= MAX ( 'Date'[Index] ) ),
'Date'[Index],
'Date'[Month],
"b", [Leftcount]
)
RETURN
SUMX ( a, [Leftcount] )```

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super Contributor

## Re: active employee count per month

After you have loaded your table ( named Data ) into Power BI, create a new table ( named DataModified) with the following DAX Query:

```DataModified =
SELECTCOLUMNS(
GENERATE(
Data,
VAR StartDate = Data[Start Date]
VAR EndDate = Data[End Date]
RETURN
CALENDAR( StartDate, EndDate - 1 )
),
"Project", Data[project],
"Name", Data[Name],
"Date", [Date]
)```

Afterwards create the calendar, projects and names dimensions:

Names = ALLNOBLANKROW( DataModified[Name] )

Projects = ALLNOBLANKROW( DataModified[Project] )

```Calendar =
CALENDAR( DATE( 2017, 1, 1 ), DATE( 2019, 12, 31 ) ),
"Month", MONTH( [Date] ),
"Month Name", FORMAT( [Date], "MMMM" ),
"Year", YEAR( [Date] ),
"Month Year Name", FORMAT( [Date], "mmm yyyy" ),
"Month Year Number", YEAR( [Date] ) * 100 + MONTH( [Date] )
)```
Then create the relationships:

Then add a mtrix visual, drop the column 'Month year Name' from the Calendar table into the rows section and this measure into the values sections:

```Active Employees =
CALCULATE(
COUNTROWS( DataModified ),
LASTDATE( 'Calendar'[Date] )
)```

Proud to be a Datanaut!

Frequent Visitor

Frequent Visitor

## Re: active employee count per month

@v-cherch-msft Im not getting the same results as you, could you please share the pbix file.

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 264 members 3,058 guests
Recent signins: