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.
I am trying to set up an HR dashboard to show headcount by month. I have a Start Date and End Date for each employee, I also inserted a cond column showing if the employee is Active (1 if still with the company, 0 if he left), and a date table. I can't figure out what else I need to show that bar chart. I also believe that I need some date column (other than StartDate or End Date) to connect the main table to the date table.
Thanks for the help!
Solved! Go to Solution.
Hi @Anonymous
Try this:
1. Make sure your Date table includes all years in the data and every year in full. No relationship between 'Date' and your data table (Table1 in the code below)
2. Set 'Date'[Month] in the rows of a matrix visual (and 'Date'[Year] as well if required)
3. Create this measure and place it in values of the matrix:
NumberActiveEmployees = VAR _FirstDayCurrentMonth = MIN ( 'Date'[Date] ) VAR _LastDayCurrentMonth = MIN ( 'Date'[Date] ) RETURN CALCULATE ( DISTINCTCOUNT ( Table1[Employee #] ), FILTER ( Table1, Table1[Hire Date] <= _LastDayCurrentMonth && ( Table1[End Date] >= _FirstDayCurrentMonth || ISBLANK ( Table1[End Date] ) ) ) )
This will count as active everyone that was employeed at least one day in that month.
Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and run some quick tests, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).
Hi @Anonymous
Try this:
1. Make sure your Date table includes all years in the data and every year in full. No relationship between 'Date' and your data table (Table1 in the code below)
2. Set 'Date'[Month] in the rows of a matrix visual (and 'Date'[Year] as well if required)
3. Create this measure and place it in values of the matrix:
NumberActiveEmployees = VAR _FirstDayCurrentMonth = MIN ( 'Date'[Date] ) VAR _LastDayCurrentMonth = MIN ( 'Date'[Date] ) RETURN CALCULATE ( DISTINCTCOUNT ( Table1[Employee #] ), FILTER ( Table1, Table1[Hire Date] <= _LastDayCurrentMonth && ( Table1[End Date] >= _FirstDayCurrentMonth || ISBLANK ( Table1[End Date] ) ) ) )
This will count as active everyone that was employeed at least one day in that month.
Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and run some quick tests, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |