cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MAJackson Frequent Visitor
Frequent Visitor

Bar Graph showing number of employees each month.

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!
1 ACCEPTED SOLUTION

Accepted Solutions
lc_finance Member
Member

Re: Bar Graph showing number of employees each month.

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:

 

Number of Active Employees.png

 

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

View solution in original post

11 REPLIES 11
lc_finance Member
Member

Re: Bar Graph showing number of employees each month.

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:

Active employees by month.png

 

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

 

Super User
Super User

Re: Bar Graph showing number of employees each month.

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
MAJackson Frequent Visitor
Frequent Visitor

Re: Bar Graph showing number of employees each month.

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

 

New Measure.png

MAJackson Frequent Visitor
Frequent Visitor

Re: Bar Graph showing number of employees each month.

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!

 

SEND PIC.png

lc_finance Member
Member

Re: Bar Graph showing number of employees each month.

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

v-lid-msft Super Contributor
Super Contributor

Re: Bar Graph showing number of employees each month.

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 Frequent Visitor
Frequent Visitor

Re: Bar Graph showing number of employees each month.

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:

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

Re: Bar Graph showing number of employees each month.

Hi @MAJackson ,

 

 

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

 

LC

lc_finance Member
Member

Re: Bar Graph showing number of employees each month.

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:

 

Number of Active Employees.png

 

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

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 375 members 3,318 guests
Please welcome our newest community members: