Multiples dates in one table

[Anonymous]

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:

Headcount = CALCULATE(DISTINCTCOUNT(Empleados[Employee]), Empleados[Active] ="Yes")

Historic Headcount=

CALCULATE(

    [Headcount],

    FILTER(

        ALLSELECTED('Date'[Date]), 'Date'[Date] <= MAX ('Date'[Date])

    ))

I have a Date table relation with Date of Admission o Employee table. Anda i have an INACTIVE relation between Date of Leave and Date table.
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!

[v-yanjiang-msft]

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 _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[amitchandak · Accepted solution]

@Anonymous , Please refer to my blog or the attached files after signatures

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

Thank uuu!

it solved my problem!