Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
AlvaroLeonardoN
Frequent Visitor

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

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?

 

AlvaroLeonardoN_0-1717527414171.png

 

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

Imagen2434.png

9 REPLIES 9
RossEdwards
Solution Sage
Solution Sage

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])   
		)
	)
)

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_CODEACTION_TYPEACTION_REASON_CODEACTION_START_DATEACTION_END_DATEDATE_HIREEXIT_DATE
36310000010000121/8/202028/2/202121/8/2020 
3631000002000721/3/202131/3/202121/8/2020 
3631000012000011/4/202131/12/202121/8/202015/4/2021
3631000012000011/1/202228/2/20221/1/2022 
3631000002000251/3/202230/4/20221/1/2022 
3631000008000141/5/202230/9/20231/1/2022 
3631000012000011/10/202329/2/20241/1/2022 
3631000002000251/3/20243/6/20261/1/2022 
37071000010000111/5/202130/6/202311/5/2021 
3707100017001251/7/20233/6/202611/5/20211 jul 2023

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.

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.

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.

AlvaroLeonardoN_3-1717528857482.png

 

 

AlvaroLeonardoN_0-1717528796808.png

AlvaroLeonardoN_1-1717528819390.png

 

Correct:

 

AlvaroLeonardoN_2-1717528844725.png

 

 

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])   
		)

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])  
        )))
 
AlvaroLeonardoN_0-1717542883706.png

 

 

 

AlvaroLeonardoN_1-1717542932001.pngAlvaroLeonardoN_2-1717542947586.png

 

 

 result correct:

tablasdim.png

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.