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

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

Regards,

Cherie

LivioLanzo

7 years ago

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:

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

10 Replies

Greg_Deckler
Community Champion
7 years ago
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
- Sal1037
  Advocate II
  6 years ago
  Greg_Deckler thank you so much for sharing your insight here. It is very helpful. Thank you!!
LivioLanzo
Solution Sage
7 years ago
Anonymous

Would you be able to post a sample data along with expected results ?
- Anonymous
  Not applicable
  7 years ago
  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
    Solution Sage
    7 years ago
    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:
    
    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: