cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vinay063 Frequent Visitor
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]))
 
My Problem is, im unable to add the Jan output i.e 7 to the Feb month. Please help here with DAX
 
2 ACCEPTED SOLUTIONS

Accepted Solutions
v-cherch-msft Super Contributor
Super Contributor

Re: active employee count per month

Hi @vinay063

 

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

1.png

 

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.
LivioLanzo Super Contributor
Super Contributor

Re: active employee count per month

Hi @vinay063

 

please follow these steps:

 

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 = 
ADDCOLUMNS(
    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:
 
2018-11-26_9-15-14.jpg
 
 
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] )
)
2018-11-26_9-17-06.jpg

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

8 REPLIES 8
Super User
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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

LivioLanzo Super Contributor
Super Contributor

Re: active employee count per month

@vinay063

 

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

vinay063 Frequent Visitor
Frequent Visitor

Re: active employee count per month

@LivioLanzo and @Greg_Deckler .

 

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.

 

 

LivioLanzo Super Contributor
Super Contributor

Re: active employee count per month

Hi @vinay063

 

Please follow these steps:

 

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 = 
ADDCOLUMNS(
    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, link them via relationships:
 
2018-11-26_9-15-14.jpg
 
 
 
 Then add this measure:
 
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:
 
2018-11-26_9-17-06.jpg

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

v-cherch-msft Super Contributor
Super Contributor

Re: active employee count per month

Hi @vinay063

 

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

1.png

 

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.
LivioLanzo Super Contributor
Super Contributor

Re: active employee count per month

Hi @vinay063

 

please follow these steps:

 

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 = 
ADDCOLUMNS(
    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:
 
2018-11-26_9-15-14.jpg
 
 
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] )
)
2018-11-26_9-17-06.jpg

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

vinay063 Frequent Visitor
Frequent Visitor

Re: active employee count per month

vinay063 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.

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 309 members 3,260 guests
Please welcome our newest community members: