cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Calculated Column for New Employee & Left Employee

Hi,

I have a table in Power BI that holds my Payroll information and it has a record per employee per month, as follows.

I would like to have a calculated column that works out the "New Employee" value and returns a 1 if the same employee number does not exist in the prior period, so in the example below, employee 90003 and 90004 return 1 for 01/02/2018 as these employee records did not exist for 01/01/2018.

I am pretty new to DAX so am struggling with how to do this, I have tried to search the web but can't seem to find anything that works for the below.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Calculated Column for New Employee & Left Employee

File attached as well

Try my new Power BI game Cross the River
4 REPLIES 4
Super User

## Re: Calculated Column for New Employee & Left Employee

You can use this column

```New Employee =
VAR temp =
FILTER (
Table1,
Table1[Employee Number] = EARLIER ( Table1[Employee Number] )
&& Table1[Period] < EARLIER ( Table1[Period] )
)
RETURN
IF ( COUNTROWS ( temp ) > 0, 0, 1 )```
Try my new Power BI game Cross the River
Super User

## Re: Calculated Column for New Employee & Left Employee

File attached as well

Try my new Power BI game Cross the River
Frequent Visitor

## Re: Calculated Column for New Employee & Left Employee

Thanks for that, it seems to work, but I do have 1 question. Does the EARLIER < ( Table1[period]) check if the employee existed in any period before? How would I change this to only look at the prior month record?

Also for browney points, what I have not asked for in the original post was the reverse logic to calculate an "Employee Left" flag which will be set to 1 if the employee does not exist in the following period, what DAX would do this?

Thanks again for your assistance

Frequent Visitor

## Re: Calculated Column for New Employee & Left Employee

Thanks very much for your help, I have managed to tweak the formula as follows to get it to work to just look at the prior period, and have also created one for "Leavers" as follows

Starter = VAR temp = FILTER ( Payroll, Payroll[EmployeeNumber] = EARLIER ( Payroll[EmployeeNumber] ) && dateadd(Payroll[FinancialDate1stDay],+1,MONTH ) = EARLIER ( Payroll[FinancialDate1stDay]) ) RETURN IF ( COUNTROWS ( temp ) > 0, 0, 1 )

Leaver = VAR temp = FILTER ( Payroll, Payroll[EmployeeNumber] = EARLIER ( Payroll[EmployeeNumber] ) && dateadd(Payroll[FinancialDate1stDay],-1,MONTH ) = EARLIER ( Payroll[FinancialDate1stDay]  ) ) RETURN IF ( COUNTROWS ( temp ) > 0, 0, 1 )

I think the above seems to be working as expected.

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,737)