cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vinay063
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

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.

View solution in original post

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!  

View solution in original post

9 REPLIES 9
LivioLanzo
Solution Sage
Solution Sage

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

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

View solution in original post

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.

View solution in original post

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

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!  

Greg_Deckler
Super User IV
Super User IV

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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




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

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors