cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

Hi @Anonymous

 

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.

View solution in original post

Hi @Anonymous

 

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!  

View solution in original post

10 REPLIES 10
LivioLanzo
Solution Sage
Solution Sage

@Anonymous

 

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!  

Anonymous
Not applicable

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

 

 

Hi @Anonymous

 

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!  

This worked for me like a charm. But I had to remove the -1 as it gave error that end date cannot before start date.  

 CALENDAR( StartDate, EndDate - 1 ) 

 Plus I really want to know how this works, to grow my understanding about dax.

Anonymous
Not applicable

Hi @Anonymous

 

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.
Anonymous
Not applicable

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

Hi @Anonymous

 

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!  

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Greg_Deckler thank you so much for sharing your insight here. It is very helpful. Thank you!!

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.