Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
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_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 |
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.
Correct:
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
result correct:
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.