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

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

Re: Calculated Column for New Employee & Left Employee

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

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
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 146 members 1,444 guests
Please welcome our newest community members: