Forum Discussion

Chinna_31's avatar
Chinna_31
New Member
1 year ago
Solved

Previous job level

I have multiple job levels for same employee and want to find previous job level , have tried with rank function which is not giving accurate information.

can you please let me know how to find that.

Need Help
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    1 year ago

    Hello Chinna_31,

     

    Can you please try this approach:

     

    1. Rank job levels for each employee by the job change date

    Job Rank = 
RANKX(
    FILTER(
        'Employee Table',
        'Employee Table'[Employee ID] = EARLIER('Employee Table'[Employee ID])
    ),
    'Employee Table'[Job Change Date],
    ,
    ASC,
    DENSE
)

    2. Find the Previous Job Level

    Previous Job Level =
VAR CurrentRank = 'Employee Table'[Job Rank]
RETURN
CALCULATE(
    MAX('Employee Table'[Job Level]),
    FILTER(
        'Employee Table',
        'Employee Table'[Employee ID] = EARLIER('Employee Table'[Employee ID]) &&
        'Employee Table'[Job Rank] = CurrentRank - 1
    )
)

    Hope this helps.

1 Reply

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    1 year ago

    Hello Chinna_31,

     

    Can you please try this approach:

     

    1. Rank job levels for each employee by the job change date

    Job Rank = 
RANKX(
    FILTER(
        'Employee Table',
        'Employee Table'[Employee ID] = EARLIER('Employee Table'[Employee ID])
    ),
    'Employee Table'[Job Change Date],
    ,
    ASC,
    DENSE
)

    2. Find the Previous Job Level

    Previous Job Level =
VAR CurrentRank = 'Employee Table'[Job Rank]
RETURN
CALCULATE(
    MAX('Employee Table'[Job Level]),
    FILTER(
        'Employee Table',
        'Employee Table'[Employee ID] = EARLIER('Employee Table'[Employee ID]) &&
        'Employee Table'[Job Rank] = CurrentRank - 1
    )
)

    Hope this helps.