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 Fin...
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
    )
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
    )