Switch Statement is not Working

[heygowtam]

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

)

[Anonymous · Accepted solution]

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