First post need help with case statement

[ConnorH]

Hi all,

This is my first post on here so if I am not posting in the right spot splease direct me. I am use to writing SQL queries but my current job does not have a data base structure so I have been working in Power BI alot. I am trying to figure out how to make a case statement such as 

"CASE
    WHEN StartDate between '7/1/18' and '9/21/18' THEN 'FY19-Q1'
    WHEN StartDate between '9/24/18' and '12/21/18' THEN 'FY19-Q2'
    WHEN StartDate between '12/31/18' and '3/22/19' THEN 'FY19-Q3'
    ELSE 'Find QTR'
END;"

-I have tried things similar to 

"Test QTR = 

Switch ( True()

'Tablename'[Start date] >=7/1/18, "FY19-Q1",

'Tablename'[Start date] >=9/24/18,"FY19-Q2"

)"

But for some reason it is saying it is saying my start date column does not exist and I get the nice red squiggly. Would really appreciate some help! 

[ibarrau]

Hi. I don't think you can use between in switch. It only asks for one value. You should get if from if from if. Like this:

IF(
    AND( Table[StartDate] > DATE(1,7,2018) , Table[StartDate] < DATE(21,9,2018) ),
    "FY19-Q1",
    IF(
        AND( Table[StartDate] > DATE(24,9,2018) , Table[StartDate] < DATE(21,12,2018) ),
        "FY19-Q2",
        IF(
            AND( Table[StartDate] > DATE(31,12,2018) , Table[StartDate] < DATE(22,3,2018) ),
            "FY19-Q3",
            "Find QTR"
        )
    )
)

You should also try creating a Calendar Fiscal Date Table. It's like a regular calendar table but with the months and quarters of your business configured.

Hope this helps,