Forum Discussion

ConnorH's avatar
ConnorH
Microsoft Employee
6 years ago

First post need help with case statement

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! 

 

1 Reply

  • ibarrau's avatar
    ibarrau
    Super User
    6 years ago

    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,