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

4 REPLIES 4
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.

 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)