cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
richardbadge Frequent Visitor
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.

 

Payroll.JPG

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculated Column for New Employee & Left Employee

@richardbadge

 

File attached as well

 

newoldemployees.png

Try my new Power BI game Cross the River

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Calculated Column for New Employee & Left Employee

@richardbadge

 

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
Super User

Re: Calculated Column for New Employee & Left Employee

@richardbadge

 

File attached as well

 

newoldemployees.png

Try my new Power BI game Cross the River

View solution in original post

richardbadge Frequent Visitor
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

Highlighted
richardbadge Frequent Visitor
Frequent Visitor

Re: Calculated Column for New Employee & Left Employee

@Zubair_Muhammad

 

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.

 

Egemeny Frequent Visitor
Frequent Visitor

Re: Calculated Column for New Employee & Left Employee

Hi @Zubair_Muhammad ,

Really usefull solution!

This was something i always wanted to include to my reporting but i believe i need to adjust to solution according to my dataset.

 

My dataset has seperate calender table, in my case the 'period' column is in the seperate table. How can i create a relationship between the period column and employee start date column in another table?

You can see the .pbix file, really appreciate your help,

 

E

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)