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
