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, I need to create a measure to count the number of the employees per month/year so I can show it on line chart etc
Here the table:
factory | name *unique key* | date | hired | term date |
1 | john | 01/01/2021 | 01/02/2020 | 31/12/2999 |
1 | john | 01/01/2021 | 01/02/2020 | 31/12/2999 |
1 | john | 02/01/2021 | 01/02/2020 | 31/12/2999 |
1 | john | 02/01/2021 | 01/02/2020 | 31/12/2999 |
1 | john | 01/04/2021 | 01/02/2020 | 31/12/2999 |
1 | john | 01/04/2021 | 01/02/2020 | 31/12/2999 |
2 | willy | 01/01/2021 | 01/02/2020 | 02/02/2020 |
2 | willy | 01/01/2021 | 01/02/2020 | 02/02/2020 |
1 | jack | 01/02/1999 | 02/01/2012 |
(jack is an one of the user than was hired and then terminated before the currently clocking system)
I dont think I can use "date" because there can be times when even if an employee is currently hired but there are no rows with his name for months(dont ask me why...), so a normal visualization with date on an axis and the count of distinct name as the other axis will lead to wrong values
I already created a AllStaff measure that calculate all staff ever employed
This count all the employees we ever had
Aslo I would like the option to count the number of employees at the end of the month (30/31), meaning if you are watching the employees of feb 2012 jack would not be counted
If anyone can help me, thanks!
Solved! Go to Solution.
Hi @tWdex ,
First create a calendar table based on your date period ,then create a month column in the calendar table;
Finally create a measure as below:
Measure =
VAR _month=SELECTEDVALUE('Calendar table'[Month])
var _maxdate=CALCULATE(MAX('Calendar table'[Date]),FILTER(ALL('Calendar table'),'Calendar table'[Month]=_month))
Return
CALCULATE(DISTINCTCOUNT('Table'[ name *unique key*]),FILTER(ALL('Table'),'Table'[factory]=MAX('Table'[factory])&&'Table'[term date]>=_maxdate&&'Table'[hired]<=_maxdate))+0
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi @tWdex ,
First create a calendar table based on your date period ,then create a month column in the calendar table;
Finally create a measure as below:
Measure =
VAR _month=SELECTEDVALUE('Calendar table'[Month])
var _maxdate=CALCULATE(MAX('Calendar table'[Date]),FILTER(ALL('Calendar table'),'Calendar table'[Month]=_month))
Return
CALCULATE(DISTINCTCOUNT('Table'[ name *unique key*]),FILTER(ALL('Table'),'Table'[factory]=MAX('Table'[factory])&&'Table'[term date]>=_maxdate&&'Table'[hired]<=_maxdate))+0
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
@tWdex , refer if my blog on HR can help
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |