Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
I have a monthly report that shows the employees information. Report has entry date and terminated date. How do I get the new hired and terminated employees in each month?
Thank you
Mildred
Solved! Go to Solution.
Hi @ma142 ,
You can refer the following links to get it:
Get the number of starters and leavers:
Starters and leavers from monthly extract
New Starters = VAR lastmonth = CALCULATETABLE ( VALUES ( Table1[Employee ID] ), FILTER ( ALL ( Table1 ), Table1[MonthNo] = MAX ( Table1[MonthNo] ) - 1 ) ) RETURN IF ( MAX ( Table1[MonthNo] ) = 1, CALCULATE ( COUNT ( Table1[Employee ID] ) ), CALCULATE ( COUNT ( Table1[Employee ID] ), FILTER ( Table1, NOT ( Table1[Employee ID] ) IN lastmonth ) ) )Leavers = VAR lastmonth = CALCULATETABLE ( VALUES ( Table1[Employee ID] ), FILTER ( ALL ( Table1 ), Table1[MonthNo] = MAX ( Table1[MonthNo] ) - 1 ) ) RETURN IF ( MAX ( Table1[MonthNo] ) = 1, 0, CALCULATE ( COUNT ( Table1[Employee ID] ), FILTER ( ALL ( Table1 ), Table1[MonthNo] = MAX ( Table1[MonthNo] ) - 1 ) ) - CALCULATE ( COUNT ( Table1[Employee ID] ), FILTER ( Table1, Table1[Employee ID] IN lastmonth ) ) )
How to calculate Starters and Leavers |DatesinPeriod |CountRows|
Get the running total of starters and leavers:
Measure =
CALCULATE (
SUM ( 'Table'[Hours] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Start Date] <= MAX ( 'Table'[Start Date] )
&& OR ( [Leave date] = BLANK (), [Leave date] >= MAX ( Table[Start Date] ) )
)
)
Cumulative Summary filtered on start date and end date
Total Number Of Staff Over Time - Power BI Insights
Best Regards
Hi @ma142 ,
You can refer the following links to get it:
Get the number of starters and leavers:
Starters and leavers from monthly extract
New Starters = VAR lastmonth = CALCULATETABLE ( VALUES ( Table1[Employee ID] ), FILTER ( ALL ( Table1 ), Table1[MonthNo] = MAX ( Table1[MonthNo] ) - 1 ) ) RETURN IF ( MAX ( Table1[MonthNo] ) = 1, CALCULATE ( COUNT ( Table1[Employee ID] ) ), CALCULATE ( COUNT ( Table1[Employee ID] ), FILTER ( Table1, NOT ( Table1[Employee ID] ) IN lastmonth ) ) )Leavers = VAR lastmonth = CALCULATETABLE ( VALUES ( Table1[Employee ID] ), FILTER ( ALL ( Table1 ), Table1[MonthNo] = MAX ( Table1[MonthNo] ) - 1 ) ) RETURN IF ( MAX ( Table1[MonthNo] ) = 1, 0, CALCULATE ( COUNT ( Table1[Employee ID] ), FILTER ( ALL ( Table1 ), Table1[MonthNo] = MAX ( Table1[MonthNo] ) - 1 ) ) - CALCULATE ( COUNT ( Table1[Employee ID] ), FILTER ( Table1, Table1[Employee ID] IN lastmonth ) ) )
How to calculate Starters and Leavers |DatesinPeriod |CountRows|
Get the running total of starters and leavers:
Measure =
CALCULATE (
SUM ( 'Table'[Hours] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Start Date] <= MAX ( 'Table'[Start Date] )
&& OR ( [Leave date] = BLANK (), [Leave date] >= MAX ( Table[Start Date] ) )
)
)
Cumulative Summary filtered on start date and end date
Total Number Of Staff Over Time - Power BI Insights
Best Regards
@ma142 there are tons of blog posts/videos this? Have you tried searching on google?
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
90 | |
84 | |
76 | |
64 |
User | Count |
---|---|
145 | |
109 | |
109 | |
102 | |
96 |