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.
Hello,
I have a problem, I searched everywhere but I don't find any solution...
I have an employees table, the employees can have multiple contracts linked to an office. There is a start_contract and end_contract fields. Beside I have a calendar table with an active relationship on the start_contract field and an inactive relationship on the end_contract one. The employees table looks like this:
employee_id | start_contract | end_contract | office_id |
1 | 2017-06-30 17:30:00 | 2017-12-30 17:30:00 | 1 |
1 | 2017-12-30 17:30:00 | 2018-06-30 17:30:00 | 2 |
2 | 2017-06-30 17:30:00 | 2017-12-30 17:30:00 | 1 |
2 | 2018-06-30 17:30:00 | 2018-12-30 17:30:00 | 2 |
I need to know when the contract is "active" by comparing the start and end date to count the number of employees active at the date the end user will filter...
I tried many things but the results are not right.
I thought it was possible to do something like this but it doesn't seems to work:
Count employees =
var selectedDate = SELECTEDVALUE(Dates[Date], now())
return COUNTROWS(GROUPBY( FILTER(Employees, Employees[start_contract] <= selectedDate && (Employees[end_contract] > selectedDate) || Employees[end_contract] = BLANK()), Employees[id]))
Right now my feeling is that it's not possible to do that with Power BI so maybe I should find a way on SQL side to get a table with only one date field and a boolean field like active_contract.
Any help is welcome
Solved! Go to Solution.
@Thibaut_G , Refer my blog and video on a similar topic. See if that can help: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://www.youtube.com/watch?v=e6Y-l_JtCq4
@Thibaut_G , Refer my blog and video on a similar topic. See if that can help: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://www.youtube.com/watch?v=e6Y-l_JtCq4
Thank you!!
Glad to see that the way I tried wasn't wrong, i didn't realize that I needed to use
max('Date'[Date])
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |