Bar Graph showing number of employees each month.

[MAJackson]

Hello,

I am trying to show the growth in head count by month for my organization. I have a detailed date table with offsets already created. My Roster table contains one column for start date and one column for end date. If they are a current employee the end date is blank.

Is there a way to look at the 2 columns and determine how many employees were active at a given month and convert that into a bar graph?

Thanks!

[v-lid-msft]

Hi @MAJackson ,

We can create a measure use the following formula to meet your requirement.

Number of Active Employees =
COUNTROWS (
    FILTER (
        ALLSELECTED ( 'Hub Roster' ),
        AND (
            'Hub Roster'[HubStartDate] <= MAX ( 'Calendar'[Date] ),
            OR (
                'Hub Roster'[HubtollofDate] >= MIN ( 'Calendar'[Date] ),
                ISBLANK ( 'Hub Roster'[HubtollofDate] )
            )
        )
    )
)

BTW, if you use ALLSELECTED ( 'Hub Roster' ) in lc_finance's formula, you can get similar result.

Best regards,

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

[MAJackson]

Hello All,

Thank you for your insights on my problem.  Unfortunatly, I have not been able to get the data to conform the correct way.  I am attaching links to a Sample Power BI file and the dummy data that is in the same format as the real data below:

• SAMPLE BI FILE
• DUMMY DATA

The Sample Bi File has the data sets already setup with one calculated measure showing New employees per month.  However when I drill into septermber should show 11 employees and not 4.  I am trying to accomplish the following:

1. A bar graph showing the total headcount ("current" employees) by month so we can see a trend of how we are growing.  September should show a headcount of 107
2. the bar graph should start on the current month and show the prior 12 months of data and roll automatically to the next month when it changes
3. It would be a bonus to see the number of new employees each month as a stacked colum

If you have any suggestions on how to accomplish the above it would be greatly appreciated.  

Again, thank you for your time and advice!

Matthew

[lc_finance · Accepted solution]

HI @MAJackson ,

you can download my proposed solution from here.

I made a few changes:

1) I slightly modified the measure formula to match the initial formula:

Number of Active Employees = VAR currentDate = MAX('Calendar'[Date]) 

RETURN SUMX ('Hub Roster', 
     VAR HubStartDate = [HubStartDate] 
     VAR HubRollOffDate = [HubRollOffDate] 
     RETURN IF (HubStartDate<=currentDate && OR(HubRollOffDate>=currentDate,HubRollOffDate=BLANK() ),1,0))

2) I removed the relationship between the Calendar table and the Employees table. As the Employees table has multiple dates (start date and end date), it's easier to do the filters using DAX than via relationship. 

3) I added a calculated column in the Calendar table to check whether the month corresponds to the past 12 months rolling. If yes, it's TRUE otherwise it's FALSE. This column is used the filter the chart automatically

Here is the formula:

12 previous months = 
VAR currentMonth = VALUE(FORMAT(TODAY(),"mm"))
VAR currentYear = YEAR(TODAY())
VAR currentMonthIsJan = currentMonth =1

VAR MonthMinus1 = IF(currentMonthIsJan,12,currentMonth-1)
VAR YearMinus1 = IF(currentMonthIsJan, currentYear-1,currentYear)
VAR YearMonthMinus1 = VALUE(YearMinus1&FORMAT(MonthMinus1,"00"))

VAR YearMonthMinus12 = VALUE((currentYear-1)&FORMAT(currentMonth,"00"))

VAR isWithin12Months = [MonthYearNum]>=YearMonthMinus12 && [MonthYearNum]<=YearMonthMinus1

RETURN isWithin12Months

4) I added a new measure to calculate the number of New Employees:

Number of New Employees = VAR currentDate = MAX('Calendar'[Date]) 
VAR currentYearMonth = VALUE(YEAR(currentDate)&FORMAT(currentDate,"mm"))

RETURN SUMX ('Hub Roster', 
     VAR HubStartDate = [HubStartDate] 
     VAR HubRollOffDate = [HubRollOffDate] 
     VAR HubStartDateYearMonth = VALUE(YEAR([HubStartDate])&FORMAT([HubStartDate],"mm"))
     RETURN IF (HubStartDateYearMonth=currentYearMonth ,1,0))

5) And finally a measure for Active employees excluding New Employees:

Number of Active Employees minus New = [Number of Active Employees]-[Number of New Employees]

Here is what it looks like:

Hopefully, this is exactly what you are looking for!

Do not hesitate if you have further questions,

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

[MAJackson]

Thank you very much!  I was able to get the test data to look exactly like I wanted it to (see image).

I am now working to replicate this in the actual report with the final data.  I updated the graph to show OCT in the image but did just read your note.  It makes perfect sense and will adjust in the final report to your recommendation.

I am glad I asked as this multi level solution would have taken me a long while to figure out.  I really appreciate your time and feedback!

Matthew

[lc_finance]

Additionally- in the proposed solution the months visualized stop at the previous month.

Example: we are in October 2019 and the months visualized stop at September 2019 (not October).

This ensures that the chart always shows the final numbers (until the 1st of November, we will not know for sure the new hires of October).

Let me know if this makes sense to you,

LC

[lc_finance]

Hi @MAJackson ,

I am working on it now, I will send you soon my proposal

LC

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[lc_finance]

Hi @MAJackson ,

You can obtain that with a calculated measure. Here is the formula you can use:

Number of Active Employees = 
VAR currentDate = MAX('Date'[Date])

RETURN 
 SUMX('Employees',
 VAR employeeStartDate = [Start Date]
 VAR employeeEndDate = [End Date]
 RETURN IF(employeeStartDate<=currentDate && OR(employeeEndDate>=currentDate, employeeEndDate=BLANK() ),1,0)
 ) 

Below a screenshot of what it would look like:

And here is a Power BI file with an example.

Let me know if this answers your questions.

Regards,

LC

Interested in Power BI templates? Check out my blog at www.finance-bi.com

[MAJackson]

Hello,

I tried the formula but I did not yeild a result (see image below).  Could you let me know what I am missing?

Thank you!

Matthew

[MAJackson]

Hello,

I refreshed my date table and got the below result.  It is still not the number of employees that were on each month. I think this may be the number of new employees?  Could you let me know what corrections I may need to make.

Thank you very much for your help!

[lc_finance]

Hi @MAJackson,

 Could you share a sample Power BI file?

You can share it via OneDrive, Google Drive, Dropbox or similar tools.

I'll take a look at it and help you understanding what's missing.

Regards,

LC

www.finance-bi.com