Forum Discussion

heygowtam's avatar
heygowtam
Helper II
3 years ago
Solved

Measure or calculated column for multiple column condition

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 

Need Help
  • danextian's avatar
    danextian
    3 years ago

    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
    )

4 Replies

  • danextian's avatar
    danextian
    Super User
    3 years ago

    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's avatar
      heygowtam
      Helper II
      3 years ago

      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's avatar
        danextian
        Super User
        3 years ago

        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
    )