Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
I just need some help organizing how to do this. I have a table that displays the employees' names, title, start date, end date and "active?". The active column just shows whether they are an active employee today or not.
What I am wanting is to be able to show (based on the start date and end date columns how many employees we had for each month. The range right now only goes back to Jan. 1, but want to get started on this. Any help is appreciated.
**I thought about just having a column for each month with a 1 or 0 if they were active during the month. This would work, except there is additional columns added for each month and this is not maintenance free (at least not how I had it). I am familiar with excel but new to power BI.
table= tblEmployees
column= EmpName
column= colStartDate
column= colEndDate
column= colActive?
Solved! Go to Solution.
Hi @joshcomputer1,
If I understand you correctly, you should be able to follow steps below to get the monthly employee count from a start and end date range.
1. Add an individual Calendar table if you don't have one yet.
Date = CALENDARAUTO()
2. Use the formula below to create a measure, and show the measure on the Chart visual with Date[Date] column as Axis.
Count of Active Employee = VAR currentDate = MAX ( 'Date'[Date] ) RETURN CALCULATE ( COUNTROWS ( Table1 ), FILTER ( Table1, ( Table1[Start Date] <= currentDate && Table1[End Date] >= currentDate ) && Table1[Active?] = 1 ) )
Here is the sample pbix file for your reference.
Regards
Do you have some sample data, which will make it easier?
You could so a measure with the following syntax below, but you would require to have a Date Table in order for this to work. Where you create a relationship your data to the Date table.
radacad.com/do-you-need-a-date-dimension
Total Employees = VAR EarliestDate = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED()) RETURN CALCULATE(DISTINCTCOUNT('tblEmployees'[colActive]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date] <= MAX('Calendar'[Date]) && 'Calendar'[Date] >= EarliestDate))
Hi @joshcomputer1,
The relationship to the date calendar is not needed here.
Could you share a sample pbix file, so that we can better assist on this issue? You can upload it to OneDrive or Dropbox and post the link here, or sent it to me in private message. Do mask sensitive data before uploading.
Regards
Team | Name | GM | SM | Role | Start Date | End Date | Active? |
First | Michael | Kayes | Bob | IT Support Agent | 3/30/2016 | 5/25/2017 | 1 |
First | Enrique | Kayes | Bob | IT Support Agent | 12/23/2015 | 5/25/2017 | 1 |
First | Jerry | Kayes | Bob | IT Support Agent | 10/21/2013 | 5/25/2017 | 1 |
First | Jim | Kayes | Bob | SM | 7/1/2012 | 5/25/2017 | 1 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |