Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

TARGET ADJUSTMENT

Hello ! 

 

I need to calculate the target adjustment based on the end date of the parental leave. 

 

I have attached an example with the "ABC" employee:

 

EMPLOYEEEMPLOYEE_PROFESSIONPATERNAL_STARTPATERNAL_END
ABCSENIOR1-Jan-211-Mar-22
ACDJUNIOR1-Feb-201-Dec-21

 

She returned from parental leave in March 2022. She will have a target reduced by 50%. Next month, the target will be 100%.

EMPLOYEE

MONTHTARGET_ADJUSTMENT_PERCENT
ABCJAN0%
 FEB0%
 MAR50%
 APR100%
 MAY100%

 

TARGET TABLE:

EMPLOYEE_PROFESSIONKPITARGET
SENIOR LOANS1000
SENIOR DEPOSITS1000
JUNIORLOANS500
JUNIORDEPOSITS250

 

How can I model the tables TO GET THE MONTHLY TARGET FOR EACH EMPLOYEE ?

 

Thanks!

Interesting Links
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    Please refer to my pbix file to see if it helps you.

    Create a date table.

    calendar = CALENDAR(DATE(2020,1,1),DATE(2022,12,31))

    Then create two columns.

    Co_month = MONTH('calendar'[Date])
    col_year = YEAR('calendar'[Date])

    Finally create a measure.

    Measure =
VAR _endmonth =
    MONTH ( MAX ( 'ABC employee'[PATERNAL_END] ) )
VAR _endyear =
    YEAR ( MAX ( 'ABC employee'[PATERNAL_END] ) )
VAR _start =
    MAX ( 'ABC employee'[PATERNAL_START] )
VAR _end =
    MAX ( 'ABC employee'[PATERNAL_END] )
RETURN
    IF (
        MAX ( 'calendar'[Date] ) >= _start
            && MAX ( 'calendar'[Date] ) < _end,
        0,
        IF (
            MAX ( 'calendar'[Co_month] ) = _endmonth
                && MAX ( 'calendar'[col_year] ) = _endyear,
            0.5,
            1
        )
    )

    If I have misunderstood your meaning, please provide more details with your desired output (It's best to use pictures like me).

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    Please refer to my pbix file to see if it helps you.

    Create a date table.

    calendar = CALENDAR(DATE(2020,1,1),DATE(2022,12,31))

    Then create two columns.

    Co_month = MONTH('calendar'[Date])
    col_year = YEAR('calendar'[Date])

    Finally create a measure.

    Measure =
VAR _endmonth =
    MONTH ( MAX ( 'ABC employee'[PATERNAL_END] ) )
VAR _endyear =
    YEAR ( MAX ( 'ABC employee'[PATERNAL_END] ) )
VAR _start =
    MAX ( 'ABC employee'[PATERNAL_START] )
VAR _end =
    MAX ( 'ABC employee'[PATERNAL_END] )
RETURN
    IF (
        MAX ( 'calendar'[Date] ) >= _start
            && MAX ( 'calendar'[Date] ) < _end,
        0,
        IF (
            MAX ( 'calendar'[Co_month] ) = _endmonth
                && MAX ( 'calendar'[col_year] ) = _endyear,
            0.5,
            1
        )
    )

    If I have misunderstood your meaning, please provide more details with your desired output (It's best to use pictures like me).

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.