Forum Discussion

heygowtam's avatar
heygowtam
Helper II
3 years ago
Solved

Switch Statement is not Working

I have two Columns [Employment Type] and [start Date]
1) if the employee is a full-time and started before Finacial Year he gets full Leave ie 20
2) If the Employee Joins the Current Finacial Year then 1.67 per month 
3) for Part time Before the financial year is 12 and for the current Finacial Year 1 per month 



I am new to Power BI, please help me if there is any other way to work around 
 
Below Statment isnt Working
 
 
 
Leaves =
SWITCH(
    TRUE(),
    'Manager Mapping'[Employment Type] = "Full Time" && 'Manager Mapping'[Start date] <= DATE(2022,07,01), 20,
    'Manager Mapping'[Employment Type] = "Full Time" && DATESBETWEEN('Manager Mapping'[Start date],01/07/2022,01/08/2022),20,
    'Manager Mapping'[Employment Type] = "Full Time" && DATESBETWEEN('Manager Mapping'[Start date],01/08/2022,01/09/2022),18.37,
    'Manager Mapping'[Employment Type] = "Full Time" && DATESBETWEEN('Manager Mapping'[Start date],01/09/2022,01/10/2022),16.70,
    'Manager Mapping'[Employment Type] = "Full Time" && DATESBETWEEN('Manager Mapping'[Start date],01/10/2022,01/11/2022),15,
    'Manager Mapping'[Employment Type] = "Full Time" && DATESBETWEEN('Manager Mapping'[Start date],01/11/2022,01/12/2022),13.36,
    'Manager Mapping'[Employment Type] = "Full Time" && DATESBETWEEN('Manager Mapping'[Start date],01/12/2022,01/01/2023),11.7,
    'Manager Mapping'[Employment Type] = "Full Time" && DATESBETWEEN('Manager Mapping'[Start date],01/01/2023,01/02/2023),10,
    'Manager Mapping'[Employment Type] = "Full Time" && DATESBETWEEN('Manager Mapping'[Start date],01/02/2023,01/03/2023),8.35,
    'Manager Mapping'[Employment Type] = "Full Time" && DATESBETWEEN('Manager Mapping'[Start date],01/03/2023,01/04/2023),6.68,
    'Manager Mapping'[Employment Type] = "Full Time" && DATESBETWEEN('Manager Mapping'[Start date],01/04/2023,01/05/2023),5,
    'Manager Mapping'[Employment Type] = "Full Time" && DATESBETWEEN('Manager Mapping'[Start date],01/05/2023,01/06/2023),3.34,
    'Manager Mapping'[Employment Type] = "Full Time" && DATESBETWEEN('Manager Mapping'[Start date],01/06/2023,01/07/2023),14.67,
    'Manager Mapping'[Employment Type] = "Part Time" && 'Manager Mapping'[Start date] <= DATE(2022,07,01), 12,
    'Manager Mapping'[Employment Type] = "Part Time" && DATESBETWEEN('Manager Mapping'[Start date],01/07/2022,01/08/2022),12,
    'Manager Mapping'[Employment Type] = "Part Time" && DATESBETWEEN('Manager Mapping'[Start date],01/08/2022,01/09/2022),11,
    'Manager Mapping'[Employment Type] = "Part Time" && DATESBETWEEN('Manager Mapping'[Start date],01/09/2022,01/10/2022),10,
    'Manager Mapping'[Employment Type] = "Part Time" && DATESBETWEEN('Manager Mapping'[Start date],01/10/2022,01/11/2022),9,
    'Manager Mapping'[Employment Type] = "Part Time" && DATESBETWEEN('Manager Mapping'[Start date],01/11/2022,01/12/2022),8,
    'Manager Mapping'[Employment Type] = "Part Time" && DATESBETWEEN('Manager Mapping'[Start date],01/12/2022,01/01/2023),7,
    'Manager Mapping'[Employment Type] = "Part Time" && DATESBETWEEN('Manager Mapping'[Start date],01/01/2023,01/02/2023),6,
    'Manager Mapping'[Employment Type] = "Part Time" && DATESBETWEEN('Manager Mapping'[Start date],01/02/2023,01/03/2023),5,
    'Manager Mapping'[Employment Type] = "Part Time" && DATESBETWEEN('Manager Mapping'[Start date],01/03/2023,01/04/2023),4,
    'Manager Mapping'[Employment Type] = "Part Time" && DATESBETWEEN('Manager Mapping'[Start date],01/04/2023,01/05/2023),3,
    'Manager Mapping'[Employment Type] = "Part Time" && DATESBETWEEN('Manager Mapping'[Start date],01/05/2023,01/06/2023),2,
    'Manager Mapping'[Employment Type] = "Part Time" && DATESBETWEEN('Manager Mapping'[Start date],01/06/2023,01/07/2023),1

)
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    HI heygowtam.

    I'd like to suggest you try to use the following calculated column formula with logic operators instead of DATESBETWEEN function:

    Leaves =
SWITCH (
    TRUE (),
    [Start date] <= DATE ( 2022, 07, 01 ),
        IF (
            [Employment Type] = "Full Time",
            20,
            IF ( [Employment Type] = "Part Time", 12 )
        ),
    [Start date] > DATE ( 2022, 07, 01 )
        && [Start date] <= DATE ( 2022, 08, 01 ),
        IF (
            [Employment Type] = "Full Time",
            20,
            IF ( [Employment Type] = "Part Time", 12 )
        ),
    [Start date] > DATE ( 2022, 08, 01 )
        && [Start date] <= DATE ( 2022, 09, 01 ),
        IF (
            [Employment Type] = "Full Time",
            18.37,
            IF ( [Employment Type] = "Part Time", 11 )
        ),
    [Start date] > DATE ( 2022, 09, 01 )
        && [Start date] <= DATE ( 2022, 10, 01 ),
        IF (
            [Employment Type] = "Full Time",
            16.70,
            IF ( [Employment Type] = "Part Time", 10 )
        ),
    [Start date] > DATE ( 2022, 10, 01 )
        && [Start date] <= DATE ( 2022, 11, 01 ),
        IF (
            [Employment Type] = "Full Time",
            15,
            IF ( [Employment Type] = "Part Time", 9 )
        ),
    [Start date] > DATE ( 2022, 11, 01 )
        && [Start date] <= DATE ( 2022, 12, 01 ),
        IF (
            [Employment Type] = "Full Time",
            13.36,
            IF ( [Employment Type] = "Part Time", 8 )
        ),
    [Start date] > DATE ( 2022, 12, 01 )
        && [Start date] <= DATE ( 2023, 01, 01 ),
        IF (
            [Employment Type] = "Full Time",
            11.7,
            IF ( [Employment Type] = "Part Time", 7 )
        ),
    [Start date] > DATE ( 2023, 01, 01 )
        && [Start date] <= DATE ( 2023, 02, 01 ),
        IF (
            [Employment Type] = "Full Time",
            10,
            IF ( [Employment Type] = "Part Time", 6 )
        ),
    [Start date] > DATE ( 2023, 02, 01 )
        && [Start date] <= DATE ( 2023, 03, 01 ),
        IF (
            [Employment Type] = "Full Time",
            8.35,
            IF ( [Employment Type] = "Part Time", 5 )
        ),
    [Start date] > DATE ( 2023, 03, 01 )
        && [Start date] <= DATE ( 2023, 04, 01 ),
        IF (
            [Employment Type] = "Full Time",
            6.68,
            IF ( [Employment Type] = "Part Time", 4 )
        ),
    [Start date] > DATE ( 2023, 04, 01 )
        && [Start date] <= DATE ( 2023, 05, 01 ),
        IF (
            [Employment Type] = "Full Time",
            5,
            IF ( [Employment Type] = "Part Time", 3 )
        ),
    [Start date] > DATE ( 2023, 05, 01 )
        && [Start date] <= DATE ( 2023, 06, 01 ),
        IF (
            [Employment Type] = "Full Time",
            3.34,
            IF ( [Employment Type] = "Part Time", 2 )
        ),
    [Start date] > DATE ( 2023, 06, 01 )
        && [Start date] <= DATE ( 2023, 07, 01 ),
        IF (
            [Employment Type] = "Full Time",
            14.67,
            IF ( [Employment Type] = "Part Time", 1 )
        )
)

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    HI heygowtam.

    I'd like to suggest you try to use the following calculated column formula with logic operators instead of DATESBETWEEN function:

    Leaves =
SWITCH (
    TRUE (),
    [Start date] <= DATE ( 2022, 07, 01 ),
        IF (
            [Employment Type] = "Full Time",
            20,
            IF ( [Employment Type] = "Part Time", 12 )
        ),
    [Start date] > DATE ( 2022, 07, 01 )
        && [Start date] <= DATE ( 2022, 08, 01 ),
        IF (
            [Employment Type] = "Full Time",
            20,
            IF ( [Employment Type] = "Part Time", 12 )
        ),
    [Start date] > DATE ( 2022, 08, 01 )
        && [Start date] <= DATE ( 2022, 09, 01 ),
        IF (
            [Employment Type] = "Full Time",
            18.37,
            IF ( [Employment Type] = "Part Time", 11 )
        ),
    [Start date] > DATE ( 2022, 09, 01 )
        && [Start date] <= DATE ( 2022, 10, 01 ),
        IF (
            [Employment Type] = "Full Time",
            16.70,
            IF ( [Employment Type] = "Part Time", 10 )
        ),
    [Start date] > DATE ( 2022, 10, 01 )
        && [Start date] <= DATE ( 2022, 11, 01 ),
        IF (
            [Employment Type] = "Full Time",
            15,
            IF ( [Employment Type] = "Part Time", 9 )
        ),
    [Start date] > DATE ( 2022, 11, 01 )
        && [Start date] <= DATE ( 2022, 12, 01 ),
        IF (
            [Employment Type] = "Full Time",
            13.36,
            IF ( [Employment Type] = "Part Time", 8 )
        ),
    [Start date] > DATE ( 2022, 12, 01 )
        && [Start date] <= DATE ( 2023, 01, 01 ),
        IF (
            [Employment Type] = "Full Time",
            11.7,
            IF ( [Employment Type] = "Part Time", 7 )
        ),
    [Start date] > DATE ( 2023, 01, 01 )
        && [Start date] <= DATE ( 2023, 02, 01 ),
        IF (
            [Employment Type] = "Full Time",
            10,
            IF ( [Employment Type] = "Part Time", 6 )
        ),
    [Start date] > DATE ( 2023, 02, 01 )
        && [Start date] <= DATE ( 2023, 03, 01 ),
        IF (
            [Employment Type] = "Full Time",
            8.35,
            IF ( [Employment Type] = "Part Time", 5 )
        ),
    [Start date] > DATE ( 2023, 03, 01 )
        && [Start date] <= DATE ( 2023, 04, 01 ),
        IF (
            [Employment Type] = "Full Time",
            6.68,
            IF ( [Employment Type] = "Part Time", 4 )
        ),
    [Start date] > DATE ( 2023, 04, 01 )
        && [Start date] <= DATE ( 2023, 05, 01 ),
        IF (
            [Employment Type] = "Full Time",
            5,
            IF ( [Employment Type] = "Part Time", 3 )
        ),
    [Start date] > DATE ( 2023, 05, 01 )
        && [Start date] <= DATE ( 2023, 06, 01 ),
        IF (
            [Employment Type] = "Full Time",
            3.34,
            IF ( [Employment Type] = "Part Time", 2 )
        ),
    [Start date] > DATE ( 2023, 06, 01 )
        && [Start date] <= DATE ( 2023, 07, 01 ),
        IF (
            [Employment Type] = "Full Time",
            14.67,
            IF ( [Employment Type] = "Part Time", 1 )
        )
)

    Regards,

    Xiaoxin Sheng