Forum Discussion
Multiples dates in one table
Hi
I have the following problem. I have a table with information of all the employees who are active and inactive of the company. In this table i have a date of admission and for those inactive employees date of leave. I want to have a visualization that shows de increase of employees through months. Example table information:
| ID | Employee | Date of admission | Date of leave | Active |
| 1 | Juan | 1/1/2019 | Yes | |
| 2 | Maria | 7/6/2003 | Yes | |
| 3 | Pedro | 6/5/2020 | 7/6/2022 | No |
| 4 | Ulises | 6/8/2018 | 8/5/2021 | No |
| 5 | Roberto | 7/9/2019 | Yes | |
| 6 | Lucia | 2/3/2015 | Yes |
Dax formula i used:
Historic Headcount=
When i show the visualization i realise that de cummulative headcount in time it is only counting for each mont employees that enter the company. So the number is always increasing. It doesent count in each month the employees which where active in a mont but now they are not active because they leave
Please Help
Thanks!
Anonymous , Please refer to my blog or the attached files after signatures
4 Replies
Anonymous , Please refer to my blog or the attached files after signatures
Hi Anonymous ,
According to your description, you want to show in the current Date from Date table, how many employees are there with active status. here's my solution.
1. Don't make any relationship between Empleados and Date table.
2. Create a measure.
Historic Headcount = CALCULATE ( DISTINCTCOUNT ( Empleados[Employee] ), FILTER ( ALLSELECTED ( Empleados ), 'Empleados'[Date of admission] <= MAX ( 'Date'[Date] ) && ( 'Empleados'[Date of leave] = BLANK () || 'Empleados'[Date of leave] >= MAX ( 'Date'[Date] ) ) ) )Get the correct result.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
