Measure or calculated column for multiple column condition

[heygowtam]

I have two Columns [employment Type], [start Date]

need to Generate another [Leave ] column Based on the following Condition,

1) if the Employee type is "Full Time" and started the previous Financial Year (before July 2022) he gets 20 leaves 

2)  if the Employee type is "Full Time" and started the CurrentFInancial Year (after July 2022) he gets 1.67 leaves per month for the remaining month (until the next Financial year )
For ex - if an employee starts in October he gets - 1.67 * 9 Months ---(July-July financial year) 

3) Same goes with Part time workers, if they start before Financial Year 12 leaves, if they start current Fincaial Year 1 per month for remaining months 


Thanks much appreciated 

[danextian]

Hi @heygowtam ,

Try this as a calculated column

Leave Credits = 
VAR startdate = 'Table'[Start Date]
VAR date1 =
    DATE ( 2022, 7, 1 )
VAR date2 =
    EDATE ( date1, 12 ) - 1
VAR credits = 1.67
VAR remainingmonths =
    DATEDIFF ( 'Table'[Start Date], date2, MONTH )
RETURN
    IF ( 'Table'[Start Date] < date1, 20, remainingmonths * credits )

Please take note that this doesn't take into consideration the possiblity that a start date can be any date in a given month so 1.67 is not pro-rated.

[heygowtam]

@danextian amazing, can you please help me to add one more condition with the same column, all you have done is for Full-time workers, we need also to consider part-time workers 

Can you please also consider Part time conditions 

it's working perfectly for Full time can you also consider Part-time (if the part-time strats before the Financial year then its 12 and if they strta in current year its 1 per month  ) 

much appreciated 

[danextian · Accepted solution]

Hello,

Please change your formula to this:

Leave Credits =
VAR emptype = 'Table'[Employment Type]
VAR startdate = 'Table'[Start Date]
VAR date1 =
    DATE ( 2022, 7, 1 )
VAR date2 =
    EDATE ( date1, 12 ) - 1
VAR annuacredits =
    IF ( emptype = "Full Time", 20, 12 )
VAR monthlycredits =
    IF ( emptype = "Full Time", 1.67, 1 )
VAR remainingmonths =
    DATEDIFF ( 'Table'[Start Date], date2, MONTH )
RETURN
    IF (
        'Table'[Start Date] < date1,
        annuacredits,
        remainingmonths * monthlycredits
    )

[heygowtam]

on the spot! Thanks @danextian