Forum Discussion

richardbadge's avatar
richardbadge
Helper I
8 years ago
Solved

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.

 

I 

 

Thanks in advance.

Need Help

5 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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 )
      • richardbadge's avatar
        richardbadge
        Helper I
        8 years ago

        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