Need Help, Calculate headcount of a fact table with several transactions

[AlvaroLeonardoN]

Hello communication, I am new to this, I have a problem, I appreciate your help.
I have a fact table that has several job actions such as promotions, salary changes, change of location, hiring and leaving employees. How do I calculate the headcount by year and month and even day considering the hire date and the exit date?  What dax formula could I use for this problem?

Link power bi https://drive.google.com/file/d/1peoq92hcEwrH6IIrbV0u9rHr4D62J2fv/view?usp=drivesdk

[RossEdwards]

Power BI works on the idea of "context", that being what rows of data match all of the combined filtering criteria.

In your graph, the context of each dot on the graph is the combination of filters on the page plus the filter being created by the date or month that use have used in your axis.

To get a total headcount, you'll need to take the context provided and tell Power BI to look over a larger sample size but use that original context to get the right answer.  This being you want to consider all records but only the ones where the hire date is before the current date (in context) and the termination data is either blank or after the current date.

Here is a sample of how you much solve this problem, which assumes you have the fact table you provided and another date table i've called "Dates":

Headcount = var CalcDate = MAX(Dates[Date])  // When on your graph, this will give you the highest date at that node
RETURN
CALCULATE(
	DISTINCTCOUNT('Employees'[Employee_Code]),  // Assuming this is what you want to count, but only count once.
	FILTER(
		ALL('Employees'),
		'Employees'[Action_Start_Date] <= CalcDate &&   // Assuming this is your hire date
		(
			'Employees'[Action_Start_Date] >= CalcDate ||     // Assuming this is your termination date
			ISBLANK('Employees'[Action_End_Date])   
		)
	)
)

[AlvaroLeonardoN]

thanks for your answer,

I updated the data and applied the formula but it did not give me the same result, help me solve it please

link to download the power bi file:

https://drive.google.com/drive/folders/1iXJz05g1IDujk9F4SGX3cWagQ3_hMM1C?usp=drive_link

EMPLOYEE_CODE	ACTION_TYPE	ACTION_REASON_CODE	ACTION_START_DATE	ACTION_END_DATE	DATE_HIRE	EXIT_DATE
36310	00001	00001	21/8/2020	28/2/2021	21/8/2020
36310	00002	00072	1/3/2021	31/3/2021	21/8/2020
36310	00012	00001	1/4/2021	31/12/2021	21/8/2020	15/4/2021
36310	00012	00001	1/1/2022	28/2/2022	1/1/2022
36310	00002	00025	1/3/2022	30/4/2022	1/1/2022
36310	00008	00014	1/5/2022	30/9/2023	1/1/2022
36310	00012	00001	1/10/2023	29/2/2024	1/1/2022
36310	00002	00025	1/3/2024	3/6/2026	1/1/2022
37071	00001	00001	11/5/2021	30/6/2023	11/5/2021
37071	00017	00125	1/7/2023	3/6/2026	11/5/2021	1 jul 2023

[RossEdwards]

Sorry Alvaro, im unable to download any links and open any files.  Hopefully the method described can get you closer to your solution.  If the method provided makes sense, hopefully you can figure out if it applies correctly to your data.

[AlvaroLeonardoN]

try again please https://drive.google.com/drive/folders/1iXJz05g1IDujk9F4SGX3cWagQ3_hMM1C

[RossEdwards]

Sorry Alvaro, I was not clear in my response and gave you the wrong impression.  I'm not allowed to download any links or open any files.

[AlvaroLeonardoN]

Thank you for your hope, I couldn't get the result, I don't know what step I'm missing or what I should eliminate.

Correct:

[RossEdwards]

I can see that you have used your hire date twice. 

The logic wont work that way.  The whole purpose is suppose to be "After and equal hire date but before and equal termination date".  You have entered it with the logic "After and equal hire date but also before and equal hire date"

The other thing thats backwards (which is my error) is which two statements are inside the OR.  That block of code should be:

Tabla[Date_Hire] >= CalcDate &&   // Assuming this is your hire date
		(
			Tabla[Exit_Date] <= CalcDate ||     // Assuming this is your termination date
			ISBLANK(Tabla[Exit_Date])   
		)

[AlvaroLeonardoN]

I applied the formula and it doesn't give me the result

Headcount = var CalcDate = MAX(calen[Date])  // When on your graph, this will give you the highest date at that node

RETURN

CALCULATE(

    DISTINCTCOUNT(Tabla[EMPLOYEE_CODE]),  // Assuming this is what you want to count, but only count once.

    FILTER(

        ALL(Tabla),

            Tabla[Date_Hire] >= CalcDate &&   // Assuming this is your hire date

        (

            Tabla[Exit_Date] <= CalcDate ||     // Assuming this is your termination date

            ISBLANK(Tabla[Exit_Date])  

        )))

 result correct:

[RossEdwards]

I wonder if your exit date isn't containing blanks.  As in your fields look empty but are maybe filled with " " instead.  This would cause it to not filter into our formula.

A simple solution could be to add another step in your Power Query to use the trim function on that exit tur.  You can right click on the column, select transform and choose Trim