cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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?

9 REPLIES 9
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])
)
)
)``````
Frequent Visitor

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

 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
Solution Sage

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.

Frequent Visitor
Solution Sage

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.

Frequent Visitor

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:

Solution Sage

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])
)``````
Frequent Visitor

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:

Solution Sage

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors