Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
richardbadge
Helper I
Helper I

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

@richardbadge

 

File attached as well

 

newoldemployees.png


Regards
Zubair

Please try my custom visuals

View solution in original post

5 REPLIES 5
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

@richardbadge

 

File attached as well

 

newoldemployees.png


Regards
Zubair

Please try my custom visuals

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

 

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

@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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.