cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## How to get the new records and dropped records?

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

1 ACCEPTED SOLUTION
Community Support

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:

Starters/leavers - cumulative

``````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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support

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:

Starters/leavers - cumulative

``````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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

@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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors