Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear Expert,
I have a table like below which lists down all the employees' Hire Date and Termination Date. Active Employee's termination date will be blank. Once they got terminated, the termination date will have value. Below is just an example:
Employee ID | Hire Date | Job | Termination Date |
123 | 5/1/2019 | HR | |
232 | 5/1/2019 | HR | |
456 | 9/5/2017 | Finance | 5/1/2019 |
534 | 5/1/2019 | Admin | |
354 | 1/14/2018 | IT | 5/1/2019 |
345 | 1/1/2019 | IT |
What I want to measure is ①Number of New Hire ② Number of Terminated Headcount.
I have another table which lists down all the dates of the year:
Date |
5/1/2019 |
6/1/2019 |
6/2/2019 |
6/3/2019 |
I want to use this Date table has the single filter to calculate those 2 measures at the same time so that I can put into single visual. The idea is like below:
New Hire Headcount = If "Hire Date" = "Date" selected, distinctcount of Employee ID.
Terminated Headcount = If "Termination Date"="Date" slected, distinctcount of Employee ID.
Which means, if the user choose 5/1/2019 in the filter. The measure for new hire will be 3 and terminated headcount will be 2.
Is it possible to achieve that?
Many thanks!
Solved! Go to Solution.
@TomLU123 set relationship between hire date and termination date with your calendar table. One of this relationship will be active and other one will be inactive.
In this case I'm assuing Hire Date relationship is Active and Terminate Date relationship is InActive.
Add following measure and you will get the count of hire and terminate head count as per date.
Hire Headcount = DISTINCTCOUNT( Table[EmployeeId] ) Terminate Headcount = CALCULATE( [Hire Headcount], USERELATIONSHIP( Table[Termination Date], Calendar[Date] ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@TomLU123 set relationship between hire date and termination date with your calendar table. One of this relationship will be active and other one will be inactive.
In this case I'm assuing Hire Date relationship is Active and Terminate Date relationship is InActive.
Add following measure and you will get the count of hire and terminate head count as per date.
Hire Headcount = DISTINCTCOUNT( Table[EmployeeId] ) Terminate Headcount = CALCULATE( [Hire Headcount], USERELATIONSHIP( Table[Termination Date], Calendar[Date] ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Sure. Two measures is probably the easiest way to get this info.
TotalHired = CALCULATE( DISTINCTCOUNT(Table1[Employee ID]), Table1[Hire Date] = SELECTEDVALUE(DateTable[Date]) )
TotalTerminated = CALCULATE( DISTINCTCOUNT(Table1[Employee ID]), Table1[Termination Date] = SELECTEDVALUE(DateTable[Date]) )
Stick those into your visual that has dates from your DateTable, and you're good to go
User | Count |
---|---|
106 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |